VLOOKUP

Formulas / VLOOKUP
Searches for data stored in the first column of a table.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value - the value to lookup
  • table_array - the range to search for the lookup value
  • col_index_num - the column number in the range to find the return value
  • range_lookup - [OPTIONAL] a logical value indicating the type of match VLOOKUP should do

Examples

  • =VLOOKUP(1004,B5:F9,4,FALSE)

    This function is used to search for the value of 1004 in the range B5:F9, and return the value in the fourth column of the row in which the value 1004 is found. In this case, the value returned is "Sue Martin".

  • =VLOOKUP(H3,B4:E13,2,FALSE)

    This function is used to search for the value of H3 in the range B4:E13, and return the value in the second column of the row in which the value H3 is found. In this case, the value returned is "First Name".

  • =VLOOKUP(H3,B4:E13,3,FALSE)

    This function is used to search for the value of H3 in the range B4:E13, and return the value in the third column of the row in which the value H3 is found. In this case, the value returned is "Last Name".

Summary

The VLOOKUP function searches for items within tables and ranges by using a lookup value and then returning the exact or approximate match found.

  • The VLOOKUP function can use wildcards for partial matching and does partial matching by default.
  • Set the range_lookup to 0 for exact matching and to 1 for approximate matching.
  • INDEX and MATCH are more flexible than VLOOKUP for two-way lookups.
  • The VLOOKUP function is not case-sensitive.


Frequently Asked Questions

What is the VLOOKUP Function?
The VLOOKUP function is used to perform lookups in tables or ranges. It works by searching for a value in the leftmost column of a table and returning a value in the same row from a designated column.
What is the XLOOKUP Function?
The XLOOKUP function is an improved version of the VLOOKUP function. Unlike VLOOKUP, it works in all directions and finds exact matches by default.
How Should Data be Organized for VLOOKUP?
Data should be organized with the look up value left of the return value. This will make the VLOOKUP function more efficient.
What is the Default Match Type for VLOOKUP?
The VLOOKUP function returns approximate matches by default.
Can VLOOKUP Return Exact Matches?
Yes, the VLOOKUP function can return exact matches, even though it returns approximate matches by default.
What Errors Does the VLOOKUP Function Commonly Throw?
The VLOOKUP function commonly throws #N/A errors. It also throws a #REF! if col_index_num is bigger than the table_array column count.

Related Functions

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.