Compare columns

Hi jkeppel,

LOOKUP() can be very useful but it will return a close match.

I generally use INDEX/MATCH to do all my lookups. You can restrict it to exact match (find value) and it is actually easier than VLOOKUP() or HLOOKUP(). It will also return an error if you set it to match a value that is not in your lookup table.

C2= INDEX(Lookup::B,MATCH(B2,Lookup::A,0))

quinn

I think quinn has a rare Sunday off so he couldn't get to your followup question.🙂

The syntax for MATCH is:

MATCH(search-for,search-where,matching-method)

In this case the 'search-for' is column B of your first table.

The 'search-where' is column G in the second table.

For INDEX in this case you only need to have column F of the second table. Delete the "extra" parameters suggested by the Formula Editor.

So you could do something like this (not tested here):

=INDEX(<column F of second table>,MATCH(B,<column G of second table>,<find value>))

SG

Hi jkeppel,

I think I threw you off calling my lookup table "Lookup". There are only the two functions INDEX and MATCH.

An easy way to build formulas in Numbers is to point and click. It will take care of the syntax for you.

So when I type INDEX I get this:

range is the column you want your values to come from. Navigate to the proper sheet and table and click the correct column. I think it is "F".

Match is going to find your row, so click on row-index and type MATCH.

With search-for highlighted click on the value you are matching. This is in the same row that the formula is in.

search-where navigate to the table you are retrieving the prices from and click on the SKU column.

matching-method is "find value".

We don't need column-index or area-index so you can ignore them.

Let me know if this does it for you (or not).

quinn

Hi jkeppel,

Glad to help and thatnks for the check!

quinn