Excel WhisperExcel Whisper

How to Use VLOOKUP Function in Excel

Complete guide to Excel's most popular lookup function with practical examples

VLOOKUP is one of Excel's most frequently used lookup functions, designed specifically for finding and matching data between tables. Learn how to use VLOOKUP through practical examples, from basic lookups to advanced techniques. Perfect for tasks like finding customer information, matching product data, or creating dynamic references.

Try VLOOKUP Online

Test VLOOKUP function directly in your browser - faster and easier than Excel

Pro Mode
Try this

Drop Excel or CSV files here

Upload up to 2 files (10MB total)โ€ข
Free Plan
Upgrade to Standard for higher limits โ†’

VLOOKUP Function Guide

Step-by-step instructions for using VLOOKUP

Function Syntax

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

Steps to Use

1

Select the cell where you want to place the formula

2

Start with =VLOOKUP(

3

Select or type the value to look up (can be a cell reference, like A2)

4

Select the entire table range (ensure lookup value is in first column and the range includes all necessary columns)

5

Enter the column number to return (counting from 1, e.g., enter 3 to return data from the 3rd column)

6

Type FALSE for exact match (recommended for most cases) or TRUE for approximate match (suitable for range lookups)

7

Press Enter to complete the formula

Use Cases

Table Information Matching

Find matching information from a reference table based on ID or keyword

Auto-fill Data

Automatically fill in related information based on lookup value

Dynamic References

Build references that update automatically with data changes

Tips & Notes

  • 1Lookup value must be in the leftmost column of your table array
  • 2Using FALSE for exact matches is recommended and provides more reliable results
  • 3Function will return #N/A error if no match is found (use IFERROR function to handle this case)
  • 4Table range must include both the lookup column and all columns you may need to return
  • 5If using TRUE for approximate match, the first column MUST be sorted in ascending order
  • 6For numeric or date lookup values, ensure consistent formatting to avoid match failures
  • 7With large datasets, exact match (FALSE) typically performs better than approximate match (TRUE)

Frequently Asked Questions about VLOOKUP

Common questions and solutions for Excel VLOOKUP function

The #N/A error typically occurs when VLOOKUP cannot find the lookup value in the first column of your table array. Check that: 1) Your lookup value exists in the first column, 2) The data formats match (text vs. numbers), 3) There are no extra spaces, and 4) You're using FALSE for exact match.

No, VLOOKUP can only return values from columns to the right of your lookup column. If you need to look up values to the left, use INDEX and MATCH functions together or the newer XLOOKUP function (in Excel 365).

Standard VLOOKUP is not case-sensitive. For case-sensitive lookups, you can use a helper column with EXACT function or use INDEX/MATCH with EXACT function.

Yes, by setting the last parameter to TRUE. However, this requires your lookup column to be sorted in ascending order. For more flexible partial matching, consider using wildcards with SEARCH or FIND functions combined with INDEX/MATCH.

VLOOKUP performance decreases with large data sets. Optimize by: 1) Use exact match (FALSE) when possible, 2) Limit the table_array range to essential columns, 3) Consider using XLOOKUP (in newer Excel versions), or 4) Convert your data to Excel Tables for dynamic references.

Instead of using hard-coded column numbers, use the COLUMNS function to dynamically calculate the column index. For example: =VLOOKUP(lookup_value, table_array, COLUMNS(A:C), FALSE) will always return the 3rd column regardless of insertions or deletions.

Standard VLOOKUP can only return one value. For multiple values, you need to either: 1) Create multiple VLOOKUP formulas, one for each column you want to return, or 2) Use more advanced array formulas with INDEX/MATCH combinations.

VLOOKUP works across worksheets by referencing the sheet name: =VLOOKUP(lookup_value, Sheet2!A:C, 3, FALSE). For different workbooks, use: =VLOOKUP(lookup_value, '[Workbook2.xlsx]Sheet1'!A:C, 3, FALSE). Ensure the external workbook is open, or use absolute file paths.