fx
FunctionPedia
Quick Answer

VLOOKUP Function

VLOOKUP searches for a value in the first column of a table array and returns a matching value from another column in the same row.

✓ Excel✓ Google SheetsExcel All versions

Real-World Business Scenarios

Scenario 1Human Resources

Employee Salary Lookup

HR managers often need to quickly find an employee's salary based on their employee ID. VLOOKUP is perfect for this task.

=VLOOKUP(A2, $D$2:$F$10, 3, FALSE)

Outcome: Returns the salary for the employee with ID in cell A2 from the employee database.

Scenario 2E-commerce

Product Price Lookup

Online stores need to display product prices based on SKU codes. VLOOKUP can retrieve prices from a product catalog.

=VLOOKUP(B2, Products!$A$2:$C$100, 3, FALSE)

Outcome: Returns the price for the product with SKU in cell B2.

Scenario 3Education

Grade Lookup from Scores

Teachers need to assign letter grades based on numeric scores. Using approximate match, VLOOKUP can find the appropriate grade.

=VLOOKUP(C2, $G$2:$H$6, 2, TRUE)

Outcome: Returns the letter grade for the score in cell C2 based on a grading scale.

Syntax

VLOOKUP
(lookup_value, table_array, col_index_num, [range_lookup])

Parameters

ParameterDescriptionRequired
lookup_valueThe value to search for in the first column of the table.Required
table_arrayThe range of cells that contains the data. The first column must contain the lookup_value.Required
col_index_numThe column number in the table_array from which to return a value.Required
range_lookupOptional. TRUE (approximate match) or FALSE (exact match). Default is TRUE.Optional

Step-by-Step Tutorial

1

Prepare your data

Organize your data in a table format with the lookup values in the first column. Make sure the first column is sorted if using approximate match.

Employees.xlsx
ABCD
1Employee IDNameDepartmentSalary
2E001Alice JohnsonMarketing75000
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
2

Enter the VLOOKUP formula

Click on the cell where you want the result to appear and type =VLOOKUP(

Employees.xlsx
fx=VLOOKUP(
ABCDEFG
1Employee IDNameDepartmentSalaryLookup IDResult
2E001Alice JohnsonMarketing75000E003=VLOOKUP(
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
=VLOOKUP(

💡 Tip: Always start formulas with an equals sign (=)

3

Enter the lookup value

Select the cell containing the value you want to search for. This is the employee ID in our example.

Employees.xlsx
fx=VLOOKUP(F2
ABCDEFG
1Employee IDNameDepartmentSalaryLookup IDResult
2E001Alice JohnsonMarketing75000E003=VLOOKUP(F2
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
=VLOOKUP(F2

💡 Tip: Make sure this value exists in the first column of your table array.

4

Select the table array

Highlight the entire table range including all columns you want to search through. Use absolute references ($) to lock the range.

Employees.xlsx
fx=VLOOKUP(F2, $A$2:$D$6
ABCDEFG
1Employee IDNameDepartmentSalaryLookup IDResult
2E001Alice JohnsonMarketing75000E003=VLOOKUP(F2, $A$2:$D$6
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
=VLOOKUP(F2, $A$2:$D$6

💡 Tip: Press F4 after selecting the range to add dollar signs for absolute referencing.

5

Specify the column index

Enter the number of the column from which to return a value. Column 1 is the first column of the table.

Employees.xlsx
fx=VLOOKUP(F2, $A$2:$D$6, 3
ABCDEFG
1Employee IDNameDepartmentSalaryLookup IDResult
2E001Alice JohnsonMarketing75000E003=VLOOKUP(F2, $A$2:$D$6, 3
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
=VLOOKUP(F2, $A$2:$D$6, 3

💡 Tip: In our example, Department is in the 3rd column of the table array.

6

Choose match type and press Enter

Enter FALSE for exact match (most common) or TRUE for approximate match, then press Enter.

Employees.xlsx
fx=VLOOKUP(F2, $A$2:$D$6, 3, FALSE)
ABCDEFG
1Employee IDNameDepartmentSalaryLookup IDResult
2E001Alice JohnsonMarketing75000E003Sales
3E002Bob SmithEngineering85000
4E003Charlie BrownSales68000
5E004Diana PrinceHR72000
6E005Eve AdamsEngineering92000
=VLOOKUP(F2, $A$2:$D$6, 3, FALSE)

Basic Example

Look up an employee's department based on their ID

=VLOOKUP(A2, $D$2:$F$10, 2, FALSE)
ResultMarketing

This formula searches for the value in cell A2 (employee ID) in the first column of the table $D$2:$F$10. When found, it returns the value from the 2nd column (Department).

Advanced Examples

Example 1: VLOOKUP with Wildcard

Partial Match

Search for products using partial names with wildcard characters.

=VLOOKUP("*" & A2 & "*", $D$2:$F$10, 3, FALSE)
Result: $29.99
The asterisk (*) wildcard matches any sequence of characters. This formula finds products that contain the text in cell A2 anywhere in their name.

Example 2: VLOOKUP with IFERROR

Error Handling

Handle cases where the lookup value is not found to avoid #N/A errors.

=IFERROR(VLOOKUP(A2, $D$2:$F$10, 3, FALSE), "Not Found")
Result: Not Found
IFERROR wraps the VLOOKUP function and returns 'Not Found' instead of #N/A when the lookup value is not found in the table.

Example 3: Approximate Match for Grades

Education

Use approximate match to find letter grades based on numeric scores.

=VLOOKUP(A2, $G$2:$H$6, 2, TRUE)
Result: B
With TRUE as the last argument, VLOOKUP finds the largest value less than or equal to the lookup value. The first column must be sorted in ascending order.

Google Sheets Deep Dive

Google Sheets Exclusive Content
<p>VLOOKUP works identically in Google Sheets with the same syntax. However, Google Sheets offers several advantages and alternatives that make it more powerful for modern spreadsheet workflows.</p><p>One key difference is that Google Sheets supports ARRAYFORMULA with VLOOKUP, allowing you to apply the function to an entire column with a single formula. Google Sheets also has INDEX/MATCH as a more flexible alternative that supports left lookups.</p>

Example 1: ARRAYFORMULA with VLOOKUP

=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, $D$2:$F$10, 3, FALSE)))

This formula applies VLOOKUP to the entire column A at once, returning results for each row automatically.

Example 2: VLOOKUP with IMPORTRANGE

=VLOOKUP(A2, IMPORTRANGE("https://docs.google.com/spreadsheets/d/.../edit", "Sheet1!A:F"), 3, FALSE)

Combine VLOOKUP with IMPORTRANGE to search across different Google Sheets files.

How VLOOKUP Works

VLOOKUP works by searching for the lookup_value in the first column of the table_array. When a match is found, it returns the value from the row where the match was found, from the column specified by col_index_num. With range_lookup set to FALSE (exact match), it finds an exact match or returns #N/A. With range_lookup set to TRUE (approximate match), it finds the largest value less than or equal to the lookup_value, but requires the first column to be sorted.

1
Select a cell
Choose where you want the result to appear.
2
Enter the formula
Type =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
3
Press Enter
The result will be displayed.

Important Notes & Limitations

  • VLOOKUP can only search to the RIGHT of the lookup column. It cannot return values from columns to the left of the lookup column.

  • The column index number breaks when columns are inserted or deleted from the table_array.

  • Approximate match requires the first column to be sorted in ascending order.

  • VLOOKUP is not case-sensitive - it treats 'Apple' and 'apple' as the same value.

Common Errors & Fixes

#N/AThe lookup value was not found in the first column

Fix: Check if the lookup value exists in the first column of your table. Make sure there are no extra spaces. Use IFERROR to handle missing values gracefully.

#REF!col_index_num is greater than the number of columns in table_array

Fix: Make sure the column index number does not exceed the number of columns in your table array.

#VALUE!col_index_num is less than 1 or range_lookup is not TRUE/FALSE

Fix: Ensure col_index_num is at least 1 and range_lookup is either TRUE, FALSE, or omitted.

Download Practice File

Practice VLOOKUP with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the VLOOKUP function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

INDEX MATCH Formula

Master the INDEX MATCH formula in Excel and Google Sheets step by step: exact syntax, left lookups, two-way lookups, and how it compares to VLOOKUP and XLOOKUP.

Master INDEX/MATCH for Two-Way Lookups

Learn how to use INDEX and MATCH together to create powerful two-way lookups that work in any direction, unlike VLOOKUP.

VLOOKUP with Multiple Criteria

VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.

Frequently Asked Questions

Can VLOOKUP search to the left?
No, VLOOKUP can only search to the right. For left lookups, use INDEX/MATCH or XLOOKUP instead.
What's the difference between TRUE and FALSE in VLOOKUP?
FALSE performs an exact match, while TRUE performs an approximate match (finds the closest value less than or equal to the lookup value).
How do I make VLOOKUP case-sensitive?
VLOOKUP is not case-sensitive by default. To make it case-sensitive, use EXACT within INDEX/MATCH: =INDEX(B:B, MATCH(TRUE, EXACT(A:A, lookup_value), 0))