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, which often causes errors. Optional.
What is the VLOOKUP function 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 EMP-104, search the master directory for that exact ID, and extract their assigned salary. We will do this by performing a standard exact match.
- Step 2: Start the Formula To extract the first salary, click the empty cell next to EMP-104 in your Marketing Budget table. Type: =VLOOKUP(
- Step 3: Select the lookup_value For your lookup_value, select the cell containing the Employee ID you want to find (A11). Then, type a comma. Type: =VLOOKUP(A11, lookup_value The specific item you are trying to find.
- Step 4: Select the table_array For your table_array, select the entire block of data in the Employee Directory (A3:D7). Then, type a comma. Type: =VLOOKUP(A11, A3:D7, table_array The entire data table where Excel will search.
- Step 5: Type the col_index_num Because the Salary is in the 4th column of our table_array (A=1, B=2, C=3, D=4), type a 4. Then, type a comma. Type: =VLOOKUP(A11, A3:D7, 4, 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 Excel to find an exact match for the Employee ID. Close the parentheses. Type: =VLOOKUP(A11, A3:D7, 4, 0) range_lookup 0 means exact match.
- Step 7: Check the First Result Press Enter. You will now see the exact salary for EMP-104 ($78,000)! Result $78,000
- Step 8: Lock the Search Area If we copy this formula down right now, our table_array (A3:D7) will accidentally slide downward and miss data. To lock it in place, click inside A3:D7 in your formula bar and press F4 on your keyboard to add dollar signs. Change This =VLOOKUP(A11, A3:D7, 4, 0) To This =VLOOKUP(A11, $A$3:$D$7, 4, 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 green square in the bottom right corner of your cell to instantly pull the salaries for the rest of your employees! Result $78,000 $82,000 $110,000
You have a master directory of hundreds of employees. How do you instantly pull the salaries for a specific list of employees into your departmental budget sheet?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee Directory | |||
| 2 | Emp ID | Name | Department | Salary |
| 3 | EMP-101 | Rahul Sharma | Engineering | $95,000 |
| 4 | EMP-102 | Priya Singh | Marketing | $82,000 |
| 5 | EMP-103 | Amit Patel | Sales | $110,000 |
| 6 | EMP-104 | Sneha Gupta | Marketing | $78,000 |
| 7 | EMP-105 | Vikram Rao | Finance | $90,000 |
| 8 | ||||
| 9 | Marketing Budget | |||
| 10 | Target Emp ID | Allocated Salary | ||
| 11 | EMP-104 | |||
| 12 | EMP-102 | |||
| 13 | EMP-103 | |||
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 instantly 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 your data changes frequently: If you frequently insert new columns into your master table, VLOOKUP will break because it relies on a hardcoded column number. Instead, 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. | Massive databases where calculating speed is critical. |
| Searches left to right | Yes | Yes | Yes |
| Looks backward (right to left) | No | Yes | Yes |
| Returns multiple columns | No | Yes | Yes |
| Search speed | Slower | Faster | Fastest |
| Excel version | All Versions | Excel 2021+ / Microsoft 365 | All Versions |
| Ease of use | Beginner | Intermediate | Advanced |
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 next largest value that is smaller than your search item. |
| 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
Does VLOOKUP work across different sheets?
Yes. You can select a table_array that lives on a completely different sheet, or even in a different workbook.
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. Double-check that your table_array is locked ($A$1), ensure there are no trailing spaces in your text, and confirm the item actually exists in the first column.
How do I use VLOOKUP from right to left?
You cannot. VLOOKUP can only search from left to right. To look backwards, you must use the XLOOKUP or INDEX/MATCH functions.
Is VLOOKUP case-sensitive?
No. VLOOKUP ignores capitalization. Searching for "apple" will perfectly match "APPLE".
Can VLOOKUP return multiple columns?
No. It only extracts a single column of data at a time. If you need to pull multiple columns instantly, 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, analytics), 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