VLookup in Excel

Function - VLookup in Excel

Vlookup in excel is a very useful in-built function in Microsoft Excel. VLOOKUP stand for Vertical Lookup and it allows you to search a specified value in the first/Leftmost column of a table vertically and retrieve a value in the same row from another column on the right side. 

VLOOKUP function in Excel may seem complicated, but it is a very handy and useful tool. Let’s discuss below:

The syntax of the VLOOKUP function is:

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

Here’s what each argument represents:

  • lookup_value: The value you want to look up/search in the first/leftmost column of the table/range of data.
  • table_array: The table/range of data in which you want to look up the data. The lookup value should always be in the first column in the range of data.
  • col_index_num: The column number in the table/range of data from which the matching value should be returned.
  • [range_lookup]: This is an optional argument. It is either TRUE or FALSE. The default value is always TRUE means an Approximate match.
    • If TRUE ( or “1”) or omitted, an approximate match is returned. If no exact match is found, it will return the closest match which is less than the lookup value.
    • If FALSE (or “0”), VLOOKUP will only find exact matches. If there are multiple exact matches, the first found is returned. It’s recommended to use FALSE for exact matching.

Note: In Excel function & formula, the value of TRUE is 1 & the value of FALSE is 0, hence, you can either put the TRUE or FALSE or their value as 1 or 0 instead.

Note: Vlookup doesn’t find the value from the left side of the Lookup Column. Therefore, table_array will be on the right side of the Lookup Column.
In other words, The column in which the lookup value exists must be in the leftmost column of the table or array of data. 
(This is the basic concept of VLookup, but we will also find the data from the left side of the Lookup Column with other tactics in this article as well.)

See the example below (table_array is B4:I21) to understand the basics of the VLookup function.
VLookup function in Excel

How To Find an Exact Match Using VLOOKUP?

VLOOKUP makes it easy to find exact matches from a table.
To find exact matches using VLOOKUP in Excel, you will need to change the value of [range_lookup] to FALSE (or “0”) because FALSE is used to find the Exact value in the Vlookup function.
Hence, Syntex will be as below:

=VLOOKUP(lookup_value, table_array, col_index_num, [FALSE or 0])

In the example below, the table array is A3:H19 to find spot name with the exact match of Athlete Name “Cindy Klassen” which is in cell J5.

Let’s discuss arguments:

=VLOOKUP(J5,A3:H19,4,FALSE)

  • Lookup_value – this argument specifies Lookup Value which is “Cindy Klassen” in cell J5.
  • table_array – This argument specifies the table array or Range of data (A3:H19).
  • col_index_num – This argument specifies the column index number from where the value should be returned. In the example, the Sport name column is 4.
  • [range_lookup] – This Last argument is a Boolean Expression. Here, the value is set to FALSE to return an exact match for the Athlete’s name.

Note: If the exact value is not found then an #N/A error is displayed.

After putting this formula in K5 and pressing enter we get the result as “Speed Skating” which is true.

 

How To Find Approximate Match Using VLOOKUP?

To find an approximate match using VLOOKUP in Excel, you can follow a similar procedure as with finding an exact match, but you need to set the [range_lookup] argument to TRUE (or “1”) or omit it. Because TRUE is used to find the approximate match value in the VLookup function. It works by finding the next largest value that is less than the lookup value we specify.

Hence, Syntex will be as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [TRUE or 1 or omit it])

In the example below, this formula will search for the closest match to “Cindy Klassen” in the leftmost column in the table or range of data (A3:H19) and return the corresponding value from the sport name column (column D).

 

Let’s discuss arguments:
=VLOOKUP(J5,A3:H19,4,TRUE)

  • Lookup_value – this argument specifies the Lookup Value which is “Cindy Klassen” in cell J5.
  • table_array – This argument specifies the table array or Range of data (A3:H19).
  • col_index_num – This argument specifies the column index number from where the value should be returned. In the example, the Sport name column is 4.
  • [range_lookup] – This Last argument is a Boolean Expression. Here, the value is set to TRUE to return an approximate match for the Athlete’s name.

After putting this formula in K5 and pressing enter we get the result as “Cycling” which is the sport name of Chris Hoy instead of Cindy Klassen.

 

How to Use VLOOKUP for Multiple Criteria?

VLOOKUP function in Excel is limited to performing lookups based on a single criteria.
If you need to use multiple criteria for a lookup, you will have to create a helping column in which you can combine multiple columns with the help of the concatenate function for the Lookup column.

For example, if you have to look up the Total Medals of Australian players in Swimming, where the country name and sport name are in two different columns, then you will have to use a helping column. This column will store the concatenated values of both columns. See the steps below:

  • First, right-click on the Leftmost column header and click on Insert. This will help you insert a column to the left of the Country column.
  • for creating the helping column, enter the formula =B3&C3 or =CONCATENATE(B4,C4) in the newly added column and rename this column as you wish. Then, drag the formula down to the rest of the cells in the column. 
  • On creating the concatenated column successfully, we can now look for the value using this column. Here, we look for the Total Medals of Australian players in Swimming.
  • Now Lookup column will be the helping column (Column A)
  • So, we enter the formula =VLOOKUP(I7&J7,A3:G19,7,FALSE) in cell K7.

The last parameter is FALSE, as we are looking for an Exact Match. On pressing enter, the Total Medals of Australian players in Swimming will be returned as 3.

Note:

  • You can use this method to Use VLOOKUP for more than two Criteria for a lookup.
  • Also, you can use this method when a duplicate value exists in the lookup column to make it unique and find the accurate result.

How to Use VLOOKUP Across Multiple Sheets?

Using VLOOKUP across multiple sheets in Microsoft Excel can be a useful way to consolidate data from different sheets into one.

Using the IFERROR or IFNA function:

  • Open the worksheet where you want to display the consolidated results. This can be a new sheet or an existing sheet.
  • Identify the common field you will use to look up data in the other sheets. This is the field that is common between the sheets. It can be a unique identifier.
  • In the worksheet where you want to place the lookup results, enter the VLOOKUP formula in the cell where you want to retrieve the data.
    Syntex will be as below:
    =IFERROR(VLOOKUP(lookup_value, sheet1!table_array, col_index_num, [FALSE or 0]), VLOOKUP(lookup_value, sheet2!table_array, col_index_num,[FALSE or 0]), …)
  • Press Enter to enter the formula.
  • Once you’ve written the formula in the first cell, you can drag it across other cells in the row or down in the column to apply the formulas to multiple cells.

 

Let’s discuss the arguments: 

  • Lookup_value – This is the value you want to look up in the table array.
  • sheet1!table_array – This is the table array on the first sheet.
  • sheet2!table_array – This is the table array on the second sheet.
  • col_index_num – This is the column number of the value you want to return.
  • [range_lookup] – This Last argument is a Boolean Expression. Here, the value is set to TRUE to return an approximate match.

Tips:

  • If you are using the IFERROR function, you can add as many VLOOKUP functions to the formula as needed to look up the value in all of the table arrays.
  • You can also use the INDEX and MATCH functions to look up values across multiple sheets. However, these functions are more complex to use than VLOOKUP.

Note:

  • Make sure that the common lookup column is in the same position (e.g., column A) in all sheets and that the result column is in the same column index number.
  • Make sure that the lookup values in the common column are unique to avoid ambiguity.
  •  

Possible Errors in the VLOOKUP Function

The VLOOKUP function in Excel is widely used to look up a value in a table and return a corresponding value from another column. However, it’s may return a few different error messages depending on the problem.

  • #N/A! error: This is the most common error and this error means “Not Available”. Possible reasons are as follows:
      • If the range_lookup argument is set to FALSE, VLOOKUP requires an exact match in the first column of the specified data range (table_array). If an exact match is not found, VLOOKUP returns an error (#N/A).
      • This could be due to a typo in your lookup value.
      • The lookup value is not available in the lookup column.
      • The lookup value is not in the first column of the data range (table_array).
      • Formatting inconsistencies (like numbers stored as text in the lookup value or lookup range).
      • The lookup value is smaller than the smallest value in the lookup column.
      • If the range_lookup argument is set to TRUE and the lookup column is not sorted in ascending (A-Z) order.
  • #REF! error: This error means “Reference”. Possible reasons are as follows:
      • This appears when VLOOKUP can not find the data table you specified in the formula.
      • The column index number is greater than the total number of columns in the data range. This means that the column index number is outside of the selected range.
      • You’ve deleted the table, which means the formula tries to reference cells that do not exist.
      • The lookup column has been moved or deleted (doesn’t mean that the content was deleted)
      • Used an incorrect cell range, 
      • You haven’t locked the cell references in the formula, so they change the data range when copied/dragged.
  • #VALUE! error: This error indicates an invalid value for the formula. There are a few reasons why this could happen with VLOOKUP:
      • The lookup value exceeds the character limit (255 characters).
      • If the specified column index is less than 1.
      • If the function is missing any parameter or parameters are not in order.
  • #NAME? error: This error means “Name”. This is less common with VLOOKUP, but it can happen if you misspell the function name or are missing quotation marks in the formula.
    To look up a person’s name, make sure you use quotation mark around the name in the formula. For example, enter the name “Fountain” in =VLOOKUP(“Fountain”,B2:E7,2,FALSE) otherwise you will get #NAME? error.
  • Circular Reference: If the VLOOKUP formula refers to the cell in which it is located, it creates a circular reference error.
  • #NULL!: This error occurs when you have provided a range_lookup value of TRUE or omitted it, and the first column of the table_array does not contain unique values.
  • #DIV! error: There are problems with the array calculation if the formula is used to calculate lookup_value or if you are using VLOOKUP within an array formula.
  • #SPILL! error: This particular #SPILL! error usually means that your formula is relying on implicit intersection for the lookup value, and using the entire column as a reference. For example, =VLOOKUP(A:A,A:C,2,FALSE).
    You can resolve the issue by anchoring the lookup reference with the @ operator like this: =VLOOKUP(@A:A,A:C,2,FALSE).
    Alternatively, you can use the traditional VLOOKUP method and refer to a single cell instead of the entire column: =VLOOKUP(A2,A:C,2,FALSE).

Points to be remembered when using VLookup

VLOOKUP function appears as an error when we type a wrong function name or add a wrong value within the function.

  • If you omit range_lookup from the syntax of VLOOKUP function, it will work as the non-exact matches. However, it will show an exact match if it exists in your data.
  • VLOOKUP is not a case-sensitive function. 
  • When adding a column after applying the VLOOKUP function, an error will appear because hard-coded index values don’t change automatically if columns are deleted or added.
  • VLOOKUP returns only the first matching value from the lookup range. This means that if there are duplicate lookup values in the lookup range, VLOOKUP function will return only the first one.
  • If you are searching for number or date values, make sure that the data in the first column of table_array is not stored as text values. Otherwise, VLOOKUP might return an incorrect value.
  • If you are searching text values, make sure that the data in the first column doesn’t contain leading spaces, trailing spaces, straight ( ‘ or ” ) and curly ( ‘ or “) citation marks, or non-printing characters. Otherwise, VLOOKUP might return an unexpected value.
    To get accurate results, try to use the CLEAN function or the TRIM function to remove trailing spaces.
  • If lookup_value is text and range_lookup is set FALSE, you can use wildcard characters  – question mark (?) and asterisk (*) – in lookup_value. For example, =VLOOKUP(“Fountain?”,B2:E7,2,FALSE) will find all instances of Fountaina with a last letter being different.
      • Question mark (?) matches any single character. 
      • Asterisk (*) matches any sequence of characters. 
      • If you want to search for an actual question mark or asterisk, type a tilde (~) sign before the wildcard character i.e. (~?) and (~*).

What is the difference between VLookup and Lookup?

VLOOKUP and LOOKUP are two different functions used to search and retrieve data from a dataset in Excel.

VLOOKUP

VLOOKUP stands for “Vertical Lookup” and is used to search for specific values in the first column of the table and retrieve a corresponding value from another column in the same row.

It requires four arguments: (lookup_value, table_array, col_index_num, and [range_lookup]).

VLOOKUP returns data from a specific column only.
  

VLOOKUP is used for both approximate and exact matches as well.

VLOOKUP can search for values in the first column only of the lookup range.

LOOKUP

LOOKUP is used to search for a specific value in a table and return a corresponding value from another column in the same row or the next smaller or larger value in the same column.

It requires two arguments only: (lookup_value and lookup_vector).

LOOKUP may return data from multiple columns in the same row or from multiple rows in the same column.

LOOKUP can be used for approximate matches.

LOOKUP can search for values in any column or row of the lookup range.

FAQs

VLOOKUP stands for “Vertical Lookup” and it is an in-built function in Excel which is commonly used for data retrieval and analysis tasks. VLOOKUP is used to search for a value in the first column of a table or data range and return a corresponding value from another column in the same row.

No, VLOOKUP is an in-built function in Excel and is designed to search values vertically in a column. To search for values horizontally in a row, you will have to use the HLOOKUP function instead.

Yes, VLOOKUP can work with multiple worksheets but it can be within the same Excel workbook. You can give the reference of the sheet name along with the cell range in the table_array argument to retrieve data from other sheets.

To avoid errors, make sure to:

  • Ensure the lookup value is present in the first column of the table.
  • Col_index_num to the correct column for the data you want to retrieve.
  • Be cautious when using the [range_lookup] parameter for approximate or exact matching, as it can lead to unexpected results.
  • Use the IFNA or IFERROR function to handle possible #N/A errors.

VLOOKUP itself doesn’t offer specific ways to handle duplicates within the formula because VLOOKUP returns the first match it finds in the lookup range. If there are duplicates, it will only return the first occurrence. However, there are a couple of approaches you can take to handle duplicates:

  1. Remove duplicates: The most straightforward approach is to remove duplicates from your data set before using VLOOKUP. You can achieve this using the built-in “Remove Duplicates” function under the Data tab in Excel.
  2. INDEX/MATCH Combination: The INDEX/MATCH combination offers more flexibility in handling duplicates. INDEX allows you to specify the exact row number to return based on the matched value in another table, while MATCH helps locate the row position of the lookup value within the table, considering exact or approximate matches depending on your preference.
    =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
  3. Use Helper column: You can use the helper column to create a unique identifier for each row, then use VLOOKUP to search for the unique identifier in the helper column. This requires additional effort but can be effective in handling duplicates.

Related Functions:

  • HLOOKUP
  • LOOKUP
  • XLOOKUP
  • MATCH
  • XMATCH
  • INDEX
  • FILTER
  • IFERROR
  • IFNA
  • IF
  • INDIRECT
  • CONCATENATE

More about VLookup:

Leave a Comment

Exit mobile version