numbers function lookup

Hi CK,

LOOKUP generally takes 3 arguments. Returning a second value from another column requires a new instance of the LOOKUP statement.

LOOKUP(search-for, search-where, result-values)

search-for, A2 the cell on 'this table' containing the value to search for.

search-where, Data::A the range of cells in which to search

result-values Data::B the range of cells from which to return the value in the same position as the value found

The formula shown below the table is the one in the selected cell, B2 of the Result table.

The formula is filled down to cell B3.

Regards,

Barry

Hi CKssS,

Instead of LOOKUP, which as ancient function from the early days of spreadsheets that has been largely replaced by the more modern equivalents of VLOOKUP and INDEX MAX, you should find things easier if you reorganize your data in a "normalized" vertical format and use SUMIF or SUMIFS. This approach is particularly helpful if you will have values for more than two dates, as instead of chaining multiple functions to address an unwieldy number of columns, you simply add new rows to the data table and the formulas automatically adjust to include the new values.

The formula in B2, copied down to B3.

=SUMIFS(Data::C,Data::A,$A2)

More on SUMIFS here.

SG

"What I get confused was why the equation worked in Cell B2 but not in Cell B3."

If the formula is identical to what's shown below your table (except for the A3 reference being to A2 in the first), I'm surprised that it does work in B2.

Revising my formula to match yours (but using the full column references rather than specifying the X2:X5 style ranges you've used), I expected a syntax error result, but got a 'LOOKUP can't find the value' error, which I found a bit confusing as well! Will probably take a further look at that is I have time this evening.

SGIII's comments regarding LOOKUP vs VLOOKUP or INDEX and MATCH are well taken. "More precise" in this context refers to the ability to specify 'exact' or 'close' matches to the search value.

LOOKUP always accepts a 'close' match if an 'exact' match isn't available.

In your case, provided any value entered into column A of the results table is an exact match for one of the values in column A of the data table, LOOKUP will do the job well. What it won't do is throw up an error flag if there's not an 'exact' match, but there is a 'close' match. In that case, LOOKUP (and VLOOKUP or MATCH, when not set to require an exact match) will take the 'close' match and return what could be a wrong value.

Regards,

Barry

Hi CKssS,

If you use LOOKUP (which generally is very rare these days; most people use the more precise VLOOKUP and INDEX MATCH) then you must chain two together as in Barry's example. I think your first one "worked" only by chance; it appears to be incorrect syntax.

As a newcomer to Numbers you can learn a lot by studying the templates at File > New in your menu. Also recommend having a look in the Help menu at Numbers Help and Formulas and Functions Help.

SG