When you learn the price of the menu below, first your eyes will look up vertically to find your favorite pie, such as The Mushroom Lover. Then your vision slides to the right to find the price for the L size – $27.99.
You’ve just done a vertical lookup, or vlookup. For this purpose, spreadsheet apps including Excel usually provide a specified function called VLOOKUP. In this article, we’ll explore how you can use the Excel VLOOKUP function, check out a few examples, and learn how to troubleshoot it.
What is VLOOKUP in Excel
VLOOKUP stands for vertical lookup. It is a function in Excel that searches vertically a specified value in a column to return a matching value, or values in the same row from different columns.
VLOOKUP syntax in Excel
=VLOOKUP("lookup_value",lookup_range, column_number, [match])
"lookup_value"is the value you want to look up vertically. Quotes are not used if you reference a cell as a lookup value.
lookup_rangeis the data range where you’ll search for the
lookup_valueand the matching value.
column_numberis the number of the column in the
lookup_range, which contains the matching value to return. For example, if your range is D7:G18, D is the first column, E is the second one, and so on.
[match]is the optional parameter to choose either closest or exact match. To return the closest match, specify TRUE; to return an exact match, specify FALSE. The closest match (TRUE) is set by default.
How to VLOOKUP in Excel – formula example
We have the following dataset with the details of courses:
Let’s search for the day when College Writing III is scheduled. Here is the VLOOKUP formula to do this:
A10– the lookup_value “College Writing III“
A2:H12– the lookup_range
6– the column number to return the matching value from
FALSE– the exact match to return
The result of the schedule for College Writing III is Thursday. And here is how the logic of this VLOOKUP formula looks:
- Excel searches the value of
A10(College Writing III) in the lookup column (
- If the value is found, Excel moves across the row to column number 6 of the specified data lookup range (
A2:H12). Column A is the first column in the lookup range, because column A contains the lookup value.
- As the formula is set to FALSE (to find the exact match) then the return value is Thursday (you can see the result in A14).
What you should know about VLOOKUP in Excel
- The VLOOKUP function executes a case-insensitive lookup.
- Excel VLOOKUP returns a #N/A if the
lookup_valueis not able to be found inside the
- Excel VLOOKUP returns a
#VALUE!error if the value of
column_numberis less than 1.
- Excel VLOOKUP returns a
#REF!error if the value of column number is greater than the number of columns in the
- The closest match (TRUE) is specified by default.
How to pull your data to Excel for vertical lookup
The example above is basic for you to understand the logic of the Excel VLOOKUP function. Your cases probably will be more advanced for the data that you can import from different sources to Excel. How? Coupler.io, an integration solution, is designed to export data from multiple apps and sources to Excel or Google Sheets or BigQuery on a schedule. You just need to sign up to Coupler.io and:
- Select and set up a source:
- Select and set up a destination (in our case – Microsoft Excel)
- Set up a schedule (optionally) if you want to automate export of your data on a custom schedule.
With your data exported from BigQuery, HubSpot, Xero, etc. to Microsoft Excel, you can easily vlookup it.
How to use VLOOKUP in Excel
There are two ways to access VLOOKUP in Excel – you can use the VLOOKUP formula in the formula bar or access it through the menu bar.
Type directly into the cell
In the example above, we used VLOOKUP in Excel this way. You need to click in the cell where you want the answer to appear, then put the cursor on Formula Bar. Type the VLOOKUP formula and all its parameters directly into the formula bar:
- In the menu bar, select the Formulas tab, then select Lookup & Reference.
- An alphabetical menu will drop down – choose VLOOKUP.
- On your right side, the Formula Builder will appear.
- Fill each box in the Formula Builder as your VLOOKUP formula parameters, then click Done.
Here is how it will look for our Vlookup formula example:
The result is the same – Thursday. So it is up to you which way is easier for you to use the VLOOKUP function 🙂
Excel VLOOKUP for an array
In the Excel VLOOKUP, you can use an array as a
lookup_value. This will let you search the matches for multiple values.
Let’s take the data set from above and vertically look up for the days when the values from the range A14:A20 are scheduled.
Here is the VLOOKUP formula to do this:
A14:A20– the lookup array
Sheet1!A2:H12– the lookup range
6– the column number to return the matching value from
FALSE– the exact match to return
Excel vlookup cases
Check out the following specific cases of how to vlookup data in Excel:
- How to VLOOKUP for multiple matches
- How to VLOOKUP for two values
- How to VLOOKUP for multiple criteria
- How to compare two columns in Excel using VLOOKUP
- How to VLOOKUP another sheet in Excel
- How to vlookup another workbook/spreadsheet in Excel
- How to vlookup multiple columns in Excel
- Excel SUMIF with VLOOKUP
Excel reverse VLOOKUP
VLOOKUP in Excel can only look up the matching values from left to right.
And what if your data set look like this and you need to look up values from right to left:
Unfortunately, in this case you can’t use VLOOKUP, but the Excel XLOOKUP function will do the job.
Note: The XLOOKUP function is only available in Excel Online and Microsoft 365.
Reverse VLOOKUP syntax
=XLOOKUP("lookup_value", lookup_array, return_array, "[if_not_found]", [match_mode], [search_mode])
lookup_valueis the value you want to look up vertically.
lookup_arrayis the data array to search the
return_arrayis the data array to return the matching value.
[if_not_found]is the optional parameter to return a specified text if the
lookup_valuehas not been found.
[match_mode]is the optional parameter to choose the match mode:
0– Exact match (default)
-1– If the exact match is not found, the next smallest item is returned.
1– If the exact match is not found, the next largest item is returned.
2– A wildcard match where
~have special meaning.
[search_mode]is the optional parameter to choose the search mode:
1– Search starting at the first item (default).
-1– Search starting at the last item.
2– Binary search that relies on
lookup_arraybeing sorted in ascending order. If not sorted, invalid results will be returned.
-2– Binary search that relies on
lookup_arraybeing sorted in descending order. If not sorted, invalid results will be returned.
Reverse VLOOKUP formula example
Let’s search for the day when Lab Only is scheduled. Here is the XLOOKUP formula to do this:
=XLOOKUP("Lab Only",H2:H12,E2:E12,"Not found")
"Lab Only"– the lookup value.
H2:H12– the data array to search for the lookup value
E2:E12– the data array to return the matching value.
"Not found"– the text to return if the lookup value has not been found.
Another way to do a reverse vlookup is using a combination of the INDEX and MATCH functions, please see this section to learn about these functions.
If your VLOOKUP formula is not working in your Excel
Sometimes when you use VLOOKUP, the
#N/A error message appears. It is the most dreaded and trickiest to handle. Here, we give the five most common reasons why your VLOOKUP is not working, including the solution for each case.
#1: Exact match in the VLOOKUP formula is not specified
On the following screenshot, we have a VLOOKUP formula, which returns an incorrect result. The correct vlookup result for ID 18 is Apple not
Enter FALSE as the last parameter if you are looking for an exact match. The correct VLOOKUP formula is
The last parameter in the VLOOKUP function is Closest match (TRUE) or Exact match (FALSE). Most people are searching for a certain match such as for customer name, product name or employee name that need an exact match. FALSE should be entered for the last parameter in your VLOOKUP formula when you are looking for an exact value.
#2: The range in the VLOOKUP formula is not locked
When you drag your working Excel VLOOKUP formula down
it returns the
You need to lock the range in your VLOOKUP formula before dragging it or copying to other cells. To do this, type
$ around the range as shown below:
Your VLOOKUP formula should look like this:
Now you can drag it without any errors expected.
Once you dragged your VLOOKUP formula, the range in it changed, which caused the error.
#3: A new column has been inserted to the range
A new column was inserted and affected the range that was specified in your VLOOKUP formula.
To avoid this in the future, you need to make the column number dynamic using the MATCH function. This allows you to retrieve the matched position of a lookup value and column number. In this way, any new inserted columns won’t affect your VLOOKUP formula.
We updated our formula, so it looks like this.
G2– the value to match in lookup range.
A1:D1– the lookup range.
0– exact match type.
Let’s test the new lookup range by adding more columns as shown in the figure below:
The result does not change.
#4: Your table has gotten bigger
Your VLOOKUP formula was made for a specific limited range. You’ll have to update the formula manually every time when the range grows with new rows. Or not?
Not only can you set the column dynamic, but also the range, by giving it a name. This will help you ensure your VLOOKUP function always checks the whole table by following these steps:
- Select the range of cells you want to use for the lookup range. Click Home → Format as Table and choose Orange, Table Style Medium 3 (or any other style you desire).
- Specify your lookup range:
$A$1:$C$12. Check the box My table has headers then click OK as shown in the screenshot below:
- Update your lookup range in the VLOOKUP formula as follows:
Let’s test the new lookup range by adding one more row. Check your VLOOKUP by searching the new ID that you just added to the lookup range. The result returns the correct value as shown in figure below:
#5: Reverse VLOOKUP for those who do not have XLOOKUP in their version of Excel
The VLOOKUP formula cannot look up a value to the left, and your Excel does not have XLOOKUP.
You can vlookup to the left using the combination of the INDEX and MATCH functions:
- Suppose you want to look up the ID for Coconut from the table below:
- Type the following formula below in cell F3:
INDEX– a function in Excel that returns the value from a certain location in a range.
A2:A8– the range column where ID is stored is from A2-A8.
MATCH– a function in Excel that used to locate the position of a lookup value in a table/column/row.
E3– where ‘Coconut’ is located.
B2:B8– the range column where Coconut is stored from B2:B8.
0– an exact match of the return value.
- The result is:
Is there any difference between VLOOKUP in Excel and Google Sheets
Basically, VLOOKUP in Excel and Google Sheets has the same logic and the same syntax. So, if you migrate to Excel from Google Sheets, you won’t have any troubles. But of course, there are some differences. Check out our blog post about VLOOKUP in Google Sheets to find them. Good luck with looking up your data!Back to Blog