Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn to work with INDEX, MATCH and HLOOKUP, as alternatives to the VLOOKUP function.
Multiple versions of this lesson are available, choose the appropriate version for you:
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Other Lookup Methods.xlsx
9.5 KB Other Lookup Methods - Completed.xlsx
9.9 KB Other Lookup Methods - Extra Practice.xlsx
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.
- Create a new worksheet
- 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
- MATCH is NOT case sensitive. (Looking up dog in a table will return Dog.)
- 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, e.g. HLOOKUP matches are NOT case sensitive. (Looking up dog in a table will return Dog.)
Lesson notes are only available for subscribers.