Excel’s VLOOKUP function is probably second only to pivot tables in popularity for management reporting and accounting users. Simon Hurst uncovers some lesser known applications.
There are many situations when you might need to find a match for a value in an Excel cell elsewhere in your workbook. Usually you need to locate the matching value cell and then return the value from a different cell in the same row or column. Lookups can also be a more elegant and flexible alternative to nested IF() functions. Intensive use of complex lookups is also an indicator that it might be worthwhile ensuring that a spreadsheet, rather than a database, is the best tool for the job.
Which lookup functions are available in Excel?
There are three actual lookup functions:
- LOOKUP() – mainly included for backward compatibility.
- VLOOKUP() checks a column of data for a value and returns a value from a different column in the row in which the match is found.
- HLOOKUP() checks a row of data for a value and returns a value from a different row in the column in which the match is found.
Because it’s more usual to arrange data in columns rather than rows, VLOOKUP() tends to be much more commonly used than HLOOKUP() so the FAQ will concentrate on VLOOKUP() – if you are using HLOOKUP() instead it just requires the mental gymnastics of moving your brain through 90 degrees.
Are there alternatives to the lookup functions?
There are two Excel functions that, when used together, can achieve similar results to the lookup functions and indeed do things that neither VLOOKUP() nor HLOOKUP() can cope with. MATCH() finds the position of a cell that matches a value and INDEX() allows us to use the value in a cell that we identify by its position.
David H Ringstrom’s recent piece on SUMPRODUCT shows how this function can be used to apply multiple criteria within a summing formula rather than using VLOOKUP.
How does a simple lookup work?