Flowchart: Which Excel LOOKUP Function Should I Use?
When you want to look up data in Excel, you can choose from various functions: LOOKUP (in two forms), VLOOKUP, HLOOKUP, and the combination of INDEX(MATCH).
Which should you use? Here’s a flowchart to help guide you:
The first and most important question is, “do you want an exact match or an approximate match?” If you want an exact match, use the first section. If not, use the second one.
If you ask for an exact match and Excel can’t find the search value, it returns a #N/A error.
If you ask for an approximate match and Excel can’t find the search value, it returns the value before the search value. This can be misleading if you really need an exact match.
If You Want an Exact Match
Notes:
“Vertical or Horizontal Lookup?” refers to the data you want Excel to scan for a match. Will you be scanning vertically down a column, or horizontally along a row from left to right to find the data you want?
“Vertical or Horizontal Data Range?” refers to the range containing the values you want Excel to return after it has found a match.
”Lookup and Data Ranges contiguous?” means, “are the above two ranges next to each other?” If both are vertical ranges, to use a VLOOKUP, the data range must be to the right of the lookup range. For horizontal ranges using HLOOKUP, the data range must be below the lookup range.
If the Lookup and Data Ranges are not arranged that way, you need INDEX(MATCH) with an exact match.
If there is a chance that you will insert or delete columns within a VLOOKUP Table_array, use INDEX(MATCH) anyway. Also use it if there is a chance that you may insert or delete rows within an HLOOKUP Table_array.
Excel 365 also has XLOOKUP. It is an extension of LOOKUP (Vector form) with extra features including exact matches. Do not use it if you want to use your spreadsheet in earlier versions of Excel.
If You Want an Approximate Match
For an approximate match, the lookup range must be sorted in ascending order. If Excel cannot find an exact match, it will return the item that is numerically or alphabetically directly before the value you asked it to find.
Notes:
“Vertical or Horizontal Lookup?” refers to the data you want Excel to scan for a match. Will you be scanning vertically down a column, or horizontally along a row from left to right to find the data you want?
“Vertical or Horizontal Data Range?” refers to the range containing the values you want Excel to return after it has found a match.
”Lookup and Data Ranges contiguous?” means, “are the above two ranges next to each other?” If both are vertical ranges, to use a VLOOKUP, the data range must be to the right of the lookup range. For horizontal ranges using HLOOKUP, the data range must be below the lookup range.
If the Lookup and Data Ranges are not arranged that way, you need INDEX(MATCH) or LOOKUP (Vector form).
If there is a chance that you will insert or delete columns within a VLOOKUP Table_array, use INDEX(MATCH) or LOOKUP anyway. Also use one of them if there is a chance that you may insert or delete rows within an HLOOKUP Table_array.