Other Lookup Methods
Working with INDEX, MATCH, and HLOOKUP as alternatives to the VLOOKUP function
When to use
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 and 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.