Other lookup methods.
Working with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.
Where/when to use the technique
INDEX and MATCH can be used in combination to create truly dynamic lookup methods, where HLOOKUP is better suited to horizontal data than VLOOKUP.
- Allows you to return a value based on the numeric row and column positions within a range.
- =INDEX(A1:C10,5,2) Returns the value in B5 (the 5th row and 2nd column of the data set)
- Works similar to VLOOKUP, but returns the numeric position of the match, allowing it to be used in INDEX functions
- The MATCH syntax (in English) is: =MATCH([what should it look for],[where is the table to look in],[guess if it can’t find a match])
- =MATCH(“Coffee”,A1:A10,FALSE) would return the numeric position of the Coffee record in cells A1:A10. This function could then be nested in the row parameter of the INDEX function.
- Note that INDEX/MATCH combinations are actually faster than VLOOKUP formulas; something that can become important in very large workbooks.
- HLOOKUP works just like VLOOKUP, only it looks across rows, then down rows, unlike VLOOKUP which looks down columns then across columns
- HLOOKUP’s syntax (in plain English) is similar to VLOOKUP’s
- =HLOOKUP([what should it look for],[where is the table to look in],[which row should it return a value from],[guess if it can’t find a match])
- All the caveats related to VLOOKUP with exact and approximate matches apply to HLOOKUP as well
Lesson notes are only available for subscribers.