Numbers Lookup Function

Derek,

I would guess you selected a range for the lookup items which is not the WHOLE column(s)... like this. Here I selected a range, then added a row in the table "ItemList":

Instead use a formula like this:

In the table on the left:

B1=VLOOKUP(A1, ItemList::A:B, 2, 0)

In this case I clicked the lookup range as the whole columns A and B by clicking (and holding) on column A, then immediately dragging to column B

To fill a formula down click once in the cell, move cursor toward the bottom of the cell, drag the yellow handle that appears:

You can also click in the cell containing the formula, command-c, select the rest of the column, command-v.

SG

Derek,

Unfortunately you example is ambiguous and I am not sure whether you are asking about filling the formula in cell B1 of the table on the left. If it is then SGIII in the previous post may have addressed how to fill down.

To reiterate that post to fill a formula down, select the cell (B1), copy, then click the column header (the letter "B") at the very top of the column), then paste.

Lookup has a one problem which is that you cannot specify exact match and will return the largest value when an exact match does not exist.

AND when the search for cell in column A is empty you will a error triangle.

I suggest using:

B1=IFERROR(VLOOKUP(A1,ItemList::A:B,2, 0),"")

this will address both problems:

Sorry, Wayne. I hadn't seen your post when I hit the Add Reply button. Thanks for the tips in the second on IFFERROR. In your second one are you saying there's a bug in VLOOKUP where it won't do an exact match any more even when you specify that?

SG

Hi Derek,

Small suggestion here. When posting a screen shot, it's nice when you have set the tables to display their names.

Regards,

Jerry

No I am saying LOOKUP() doesn't seem to have exact match

Wayne Contello wrote:

No I am saying LOOKUP() doesn't seem to have exact match

Lookup never did have an argument to choose exact or close match.

Jerry

Oh, I see, LOOKUP vs VLOOKUP. I haven't used LOOKUP much. Why not use VLOOKUP, which does have an exact match?

SG

SG,

The functions list and their definitions are victims of the desire for legacy system compatibility.

They appear to defy logic if you only consider the feature set in a snapshot.

Jerry

Jerry,

I think I follow. I've never paid much attention to LOOKUP, which must have its advantages. Won't VLOOKUP work here, set to look for an exact match?

SG

SG,

Sure, it can be made to work and starting a document from scratch you could use either, but they are both present for compatibility reasons.

Jerry

Jerry, I don't understand:

Sure, it can be made to work and starting a document from scratch you could use either, but they are both present for compatibility reasons.

SG. I suggested vlookup in the formula I posted earlier:

B1=IFERROR(VLOOKUP(A1,ItemList::A:B,2, 0),"")