VLOOKUP Function in Excel
- lookup_value: (What to look for) The specific item you are trying to find. It can be text, a number, or a cell reference (like selecting cell A2). Required.
- table_array: (Where to look) The entire data table or cell range where Excel will search. VLOOKUP will automatically search for the lookup_value in the very first column of this range. Required.
- col_index_num: (Which column to return) A simple number indicating which column of the table_array contains your answer. For example, if your table range is A to C, and your answer is in column C, type a 3. Required.
- range_lookup: (Exact or Approximate match) A rule that tells Excel how strict to be when searching. See Common Criteria Patterns in VLOOKUP below. Type FALSE or 0 for an exact match (e.g., finding exactly "Apple"). Type TRUE or 1 for an approximate match (e.g., finding the closest tax bracket). If you leave this blank, Excel defaults to an approximate match, this is the mistake behind most wrong VLOOKUP results. Optional.
What does VLOOKUP do in Excel?
The Excel VLOOKUP function searches for a specific value in the first column of a table and returns data from the same row in another column.
Think of it like a phone book: you look up a person's name (the specific value), slide your finger across the row, and extract their phone number.
Learn with Example: VLOOKUP
- Step 1: Understand the Goal We need the formula to look up INV-1001, search the bank's master list of paid invoices, and extract the "Paid" status. If the invoice is not in the bank list, Excel shows #N/A, which tells us it is unpaid.
- Step 2: Start the Formula Click the empty cell next to INV-1001 in your working table. Type: =VLOOKUP(
- Step 3: Select the lookup_value Select the cell containing the Invoice number you want to check (A9). Then, type a comma. Type: =VLOOKUP(A9, lookup_value The specific item you are trying to find.
- Step 4: Select the table_array Select the entire block of data in the Paid Invoices table (A3:B5). Then, type a comma. Type: =VLOOKUP(A9, A3:B5, table_array The entire data table where Excel will search.
- Step 5: Type the col_index_num Because the Status is in the 2nd column of our table, type a 2. Then, type a comma. Type: =VLOOKUP(A9, A3:B5, 2, col_index_num A number indicating which column of the table_array contains your answer.
- Step 6: Set the range_lookup Type a zero to force an exact match for the invoice number. Close the parentheses. Type: =VLOOKUP(A9, A3:B5, 2, 0) range_lookup FALSE (or 0) finds an exact match; TRUE (or 1) finds the closest match.
- Step 7: Check the First Result Press Enter. You will now see the bank status for INV-1001 (Paid)! Result Paid
- Step 8: Lock the Search Area Click inside A3:B5 in your formula bar and press F4 on your keyboard to lock the bank list in place. Change This =VLOOKUP(A9, A3:B5, 2, 0) To This =VLOOKUP(A9, $A$3:$B$5, 2, 0) Hint: Do NOT lock the lookup value! We want it to slide down to the next rows when we drag the formula.
- Step 9: Copy Down and Check the Final Result Double-click the small square in the bottom-right corner of the cell (the fill handle) to copy the formula down. You will immediately see "Paid" next to the settled invoices, and #N/A next to INV-1002 and INV-1005: those are the unpaid ones. Result Paid #N/A Paid Paid #N/A Tip The #N/A errors are useful here, they flag your unpaid invoices. To show a clean label like "Unpaid" instead, wrap your VLOOKUP in IFERROR, exactly like the Operations example.
You have a list of paid invoices from your bank, and a master list of all generated invoices in your system. How do you find out which invoices have NOT been paid yet?
| A | B | |
|---|---|---|
| 1 | Paid Invoices | |
| 2 | Invoice # | Status |
| 3 | INV-1001 | Paid |
| 4 | INV-1003 | Paid |
| 5 | INV-1004 | Paid |
| 6 | ||
| 7 | All Generated Invoices | |
| 8 | Invoice # | Bank Status |
| 9 | INV-1001 | |
| 10 | INV-1002 | |
| 11 | INV-1003 | |
| 12 | INV-1004 | |
| 13 | INV-1005 | |
When to use VLOOKUP
Here is when you should use the VLOOKUP function:
- Extracting specific details: If you need to extract employee salaries from an HR master list into your working budget sheet.
- Building dashboards: If you need to display a product's price, stock, and category when someone types in a Product ID.
- Assigning pricing tiers: If you need to automatically assign shipping costs or commission rates based on a scale.
When not to use VLOOKUP
Here are the specific situations where you should avoid VLOOKUP:
- When your search column is not the very first column: VLOOKUP cannot look backward. Instead, use XLOOKUP.
- When you often insert columns: VLOOKUP relies on a fixed column number, so a new column makes it pull from the wrong place. Use XLOOKUP.
- When your data is horizontal: If your headers run down the side instead of across the top. Instead, use HLOOKUP.
- When you need to extract multiple columns at once: Instead, use the newer XLOOKUP or the FILTER function.
VLOOKUP vs XLOOKUP vs INDEX & MATCH
Here is how VLOOKUP compares to other lookup formulas.
| Feature | VLOOKUP | XLOOKUP | INDEX & MATCH |
|---|---|---|---|
| Best used for | Legacy spreadsheets and basic vertical lookups. | Almost every lookup scenario. It is the modern replacement. | Older Excel versions that need to look backward or return several columns. |
| Searches left to right | Yes | Yes | Yes |
| Looks backward (right to left) | No | Yes | Yes |
| Returns multiple columns | No | Yes | Yes |
| Speed on large data | Slower | Faster | Faster |
| Excel version | All Versions | Excel 2021+ / Microsoft 365 | All Versions |
| Ease of use | Beginner | Beginner | Intermediate |
Bottom Line: Use VLOOKUP if you are working on an older version of Excel, but use XLOOKUP instead if you have a modern version of Excel (2021 or newer) for more flexibility and speed.
Common Criteria Patterns in VLOOKUP
Here are the most common rules used in the range_lookup parameter.
| Criteria Pattern Name | Formula Example | Explanation |
|---|---|---|
| Exact match | =VLOOKUP(A2, C:E, 2, FALSE) | Forces Excel to find an exact match for your search item. If it cannot find it, it returns an error. This is the most common use. |
| Approximate match | =VLOOKUP(A2, C:E, 2, TRUE) | Finds the exact match, or the largest value that is smaller than your search item (needs the first column sorted). |
| Wildcard search | =VLOOKUP("*"&A2&"*", C:E, 2, FALSE) | Finds an item that contains a specific word or phrase. You MUST use FALSE for wildcards to work. |
For a complete list of patterns (including dates, wildcards, and cell references), see our full Excel Criteria and Wildcards reference page.
Common Mistakes When Using VLOOKUP
#N/A or #NAME?) and how to fix them, see our Common Excel Errors Troubleshooting guide.Frequently Asked Questions about VLOOKUP
Quick answers about VLOOKUP
How do I do VLOOKUP between two sheets?
Point table_array at the other sheet, for example =VLOOKUP(A2, Prices!A:C, 3, 0). Click the other sheet while typing and Excel adds its name; then lock the range with F4.
Why is VLOOKUP returning the wrong value?
This almost always happens because you forgot to type a 0 or FALSE at the end of your formula to force an exact match, or because your data isn't sorted when performing an approximate match.
Why does VLOOKUP return #N/A?
Excel cannot find your item. Check three things
- The item really exists in the first column of your table_array
- There are no extra spaces in the text
- Numbers are not stored as text
If the first row works but the rows below show #N/A, your table_array is not locked: press F4 on it and copy down again.
Is VLOOKUP case-sensitive?
No. VLOOKUP ignores capitalization. Searching for "apple" will match "APPLE".
Can VLOOKUP return multiple columns?
No. It only extracts a single column of data at a time. If you need to return multiple columns at once, use the XLOOKUP function instead.
Can VLOOKUP return multiple results?
No. VLOOKUP will always stop searching and return the very first match it finds, even if there are multiple identical items in your list.
VLOOKUP Practice Workbook
Download ready-to-use Excel files and practice VLOOKUP hands-on
- All 5 worked examples (Finance, Sales, HR, Operations, Data), each with a master table and a report table to fill in
- Blank result cells to write each VLOOKUP yourself, plus the locked ranges set up for copy-down
- An answer key to check every returned value