Because this kind of problem appears in almost any industry, VLOOKUP is used in all kinds of businesses.
This article describes how to use VLOOKUP in simple language.
VLOOKUP only looks right
VLOOKUP can only look to the right.
Two match modes
VLOOKUP has two match modes: exact match and approximate match.
The match mode used by VLOOKUP is controlled by the last argument,range_lookup.
Approximate match example
In some cases, you will need anapproximate matchlookup instead of anexact matchlookup.
A good example is the problem of assigning a letter grade based on a score.
This happens becauserange_lookupis optional anddefaults to TRUE.
This can cause big problems if you expect an exact match, as seen in the worksheet below.
Here, the goal is to look up the amount for a given invoice number in cell F5.
Notice that invoice number 100235does not exist in the data, but VLOOKUP returns 12,000.
In the screen below, VLOOKUP is configured to find the price for the color “Green”.
To retrieveall matches, use theFILTER function.
To use wildcards with VLOOKUP, you must provide FALSE or zero (0) forrange_lookup.
Two-way lookup
Inside the VLOOKUP function,column_index_numis normally hard-coded as a static number.
However, you’re able to create adynamic column indexby using theMATCH functionto locate the desired column.
This technique allows you to create a dynamic two-way lookup, matching on both rowsandcolumns.
For more details,see this example.
Note: In general,INDEX and MATCHis a more flexible way toperform two-way lookups.
Multiple criteria
The VLOOKUP function does not handle multiple criteria natively.
The formula in H6 is:
For details,see this example.
For a more advanced, flexible approach,see this example.
Note:INDEX and MATCHandXLOOKUPare better for lookups based on multiple criteria.
The #N/A error simply means “not found”.
To return nothing (i.e.
However, use caution with IFERROR, because it will catchany error, not just the #N/A error.
Notes: (1) This approach is overkill unless performance is a problem.
(2) newer functions likeXLOOKUPhave built-in support for a fast binary search.
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
This makes FILTER a flexible way to isolate and inspect data without altering the…
