Hi Beuhlig,
Glad the example helped, and thanks for the green tick!
My advice is to avoid LOOKUP. It seems so straightforward but it's a ancient function from the dawn of spreadsheets that has superior modern equivalents in VLOOKUP, HLOOKUP and, especially, INDEX MATCH.
One of the problems with LOOKUP is that it is not explicit about the type of match. So you can end up with a "match" when you don't really want one, or you need a different type of match.
Here's a simple illustration:
LOOKUP will return a match even if you don't want a value returned when there is no exact match! And LOOKUP always matches on the largest value less than or equal to the lookup value, even if you don't want that. It gives you no warning and you have no choice.
The MATCH in INDEX MATCH, on the other hand, makes available a third parameter (0,1, and -1) that allows you to be more explicit. With 0 you can specify that you really do want an exact match, and want to be alerted if there is no exact match. With 1 you can specify that you want a match on the largest value less than equal to your lookup value (what LOOKUP always does). With -1 you can specify you want a match on the smallest value greater than or equal to your lookup value. That can be handy if you want the "first" match in a column, here the A. (LOOKUP will only give you the "last," the B.)
VLOOKUP (and HLOOKUP) give you similar control to INDEX MATCH but require your columns (or rows) to be in a specific order, which is often inconvenient.
So if you can gain a basic understanding of INDEX MATCH you will find your spreadsheet life simpler and less confusing in the end, even if at first glance the combination seems more complicated than the other lookup methods.
SG
P.S. The formulas pasted below in case you want to play with them:
Formula Used | Type of match |
LOOKUP(A2,Table 1::B,Table 1::A) | inexact - find largest value |
LOOKUP(A2,Table 1::B,Table 1::A) | inexact - find largest value |
INDEX(Table 1::A,MATCH(A3,Table 1::B,1)) | inexact - find largest value |
INDEX(Table 1::A,MATCH(A4,Table 1::B,-1)) | inexact - find smallest value |
INDEX(Table 1::A,MATCH(A5,Table 1::B,0)) | exact - find value --> error |
INDEX(Table 1::A,MATCH(A6,Table 1::B,0)) | exact - find value |
INDEX(Table 1::A,MATCH(A8,Table 1::B,-1)) | inexact - find smallest value |
INDEX(Table 1::A,MATCH(A9,Table 1::B,1)) | inexact - find largest value |
