VLOOKUP Function in Excel helps to lookup the corresponding values of a Range,Table and Arrays. VLookup match the given lookup value in the first column of the target range and returns the corresponding value of the specified column. It performs the vertical lookup from left to right columns of a table.
In this topic:
- Syntax Examples
- Return Values
- Best Practices
- Point to Remember
- Download Example Files
Here is the VLOOKUP function in Excel to lookup for a value in a Range, Array or Table. And return the respective column value of the matching position.
=VLOOKUP(Value to Find, Target Range, Table or Array, Index Column Number, Exact or Approximate Match Type)
Following is the Syntax of the Excel VlookUp function. VLookUp have 3 required parameters and one optional parameter.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VlookUp function takes four parameters. lookup value, table array, index number and range lookup.
- lookup_value: The value to find in the first column of the range, array or table. lookup value could be a value, string, date, cell reference, named range.
- table_array: Range, Table or An Array with one or multiple columns. This is the array in which you are looking for the lookup_value. Table_array could be a Range,Named Range, Table or an Array.
- col_index_num: Column number in the target Range, Table or An Array from which the Corresponding matching value lookup_value to be returned.
- approximate_match: An Optional Parameter to Specify whether you want perform an exact match or an approximate match. You can enter False to search for exact match and return the corresponding value. TRUE returns the value of an approximate match. (If this parameter is omitted, TRUE is the default).
Here is the Syntax of VlookUp explained with Examples. We can use VLookUp function to find a value in the left most column of the table array and return the corresponding value of the given column.
1. VLookUp Syntax to Return Exact Match:
Following is the Syntax and Example to search for a value in a range and return the value in the 2nd column.
Syntax: =VLOOKUP(lookup_value, Range, 2, FALSE)
Example: =VLOOKUP(“North”, Range(“A2:B6”, 2, FALSE)
‘OR =VLOOKUP(D2, Range(“A2:B6″, 2, FALSE)
This will find the Region”North” in Column A (A2:A6) and return the corresponding value in the Column B (B2:B6). It will return #NA if it finds no match.
2. VLookUp Syntax to Return Approximate Match:
Following is the Syntax and Example to search for a value in the target array and return the corresponding value in the 2nd column.
Syntax: =VLOOKUP(lookup_value, Range, 2, TRUE)
Example: =VLOOKUP(“Some”, Range(“A2:B6”, 2, FALSE)
This will search for the given Region name in column A (A2:A6) and return the corresponding value in the Column B (B2:B6). It will return approximate search result if it finds no match.
VLOOKUP Function is one of the most using and powerful formulas in the Excel. Often, we search for a value in a array of values and find the corresponding value for further process. For example, we can find Sales of a given Department. We can search for a name in the huge directory and list the respective column values in a range.
- Search for a String and Return the Column Value
- Check if a Value exist in a Row or Column
- Return the information from Table Fields of a matching record
- Find a value in an Array of Values and Return respective data from multi dimensional Array
VLOOKUP Usage Steps & Instructions
Many Excel Users wants to understand How to Use VlookUp? Here are the Simple instructions and Examples to explain the usage of VLOOKUP Function in nutshell. We can specify the lookup value, lookup range, lookup column and match type to return the matched values.
- First Argument: Specify the Lookup value to Search for in the left most column of the given range, table or array. Example: G1
- Second Argument: Enter the Range, Reference, Named Range, Table or any Array as table_array to look for the first argument (lookup_value). Example: A2:D20
- Third Argument: Column Number from the Left side of the Table Array (Second Argument) which you wants Excel to pick and return the corresponding value of the matched record. Example: 4
- Fourth Argument: TRUE or FALSE to tell the Excel to perform Approximate or Exact Match. In most cases, it is FALSE (We want to return the column value of the Exact Match). Example: FALSE. DO NOT omit this argument if you want to perform Exact Match, as the default value is TRUE (Approximate Match)
Here is the Example File, VlookUp function is explained with Syntax, Examples and Uses with Simple Step by Step Instructions.
VLOOKUP Function return the Following Values when you pass the arguments to the Function.
- Returns the columns value of the Exact Match when it find the lookup_value
- It will return the #NA when the lookup_value is not found in the left most column of the table_array
- If you pass the last parameter as TRUE, it will return the nearest value when it is not found the exact match
Here are the best practices to be followed while using Excel Vlookup Function.
- Match type: Most Cases we search for Exact Match and return its correspond=ding Value. Please Make sure to set the fourth Parameters to FALSE (or 0) for Exact Match.
- Reference Type: We refer Cells, Rows, Columns, Ranges, Sheets, Workbooks, Named Ranges, Tables, Table Fields while creating VLookUp Formula. Make sure that you are using right type reference (Relative and Absolute Cell References)
- Error Handling: VLOOKUP troughs #NA Error if lookup values is not in the first column of the table array data. We can use the Functions IFERROR, IFNA, ISNA Function can help to handle if the return value is an error data type.
Points to Remember:
Please remember and note the following Points while using Excel Vlookup Formula. Understanding the below points will help you to avoid Issue and troubleshooting the Formula.
- VLOOKUP is Case sensitive
- It looks from left to right
- Returns the first matched instance
- Can not handle multiple Criteria. Alternatively, use Index, Match functions and create Array formula
VLOOKUP Formula with Example
Download Example Files
- Excel VlookUp in Mac
- VlookUp Tutorial – Step by step
- VlookUp Between Sheets
- VlookUp for Dummies
- Combine IF and VLOOKUP
- Combine IFERROR and VLOOKUP
- VLOOKUP for Combined Values
- Troubleshoot the VLOOKUP formula
- Problems When Sorting VLOOKUP formula
In most scenarios, we use VLookUp function with the following Excel formulas
- XLOOKUP: Latest and dynamic Excel Builtin Formula to Lookup the Values in any direction.
HLOOKUP: HLookUp Performs the Horizontal lookup in excel. It will search for a lookup value in the first row of the table and returns the corresponding value of the specified Row in the target range.
- IFERROR: We can handle the #NA Error using IfError and VLOOKUP
- IF: If Function helps to check if the return value is matching with required value and take the decision.
- MATCH : MATCH Function in Excel used to find the match position of a lookup value in a Range, Table and Array.
- INDEX : Excel INDEX returns the value of a given position of a Range, Column, Row, Table and Array.
We create Advanced VLOOKUP Formulas by using MATCH and INDEX functions together .