Excel VLOOKUP Function: How to Use VLOOKUP Function in Office 365?

Jenny

Excel VLOOKUP Function:

This tutorial demystifies the description, basic syntax, and usage of the Excel VLOOKUP function in Office 365.

https://img.particlenews.com/image.php?url=3o8Prh_0dVnDqWn00
Excel VLOOKUP FunctionAndrews- GeekExcel

Description of the VLOOKUP Function:

  1. The VLOOKUP (Vertical Lookup) function looks up data in a range or table by row.
  2. It searches for the value in the first column of the table and returns the value in the same row as per the col_index_num position.
  3. This function supports exact, approximate, and wildcards (*?) for partial matches.

VLOOKUP Function – Syntax:

The basic syntax of the VLOOKUP function is,

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

Argument Explanation:

  • 1) lookup_value (Required) – The value you want to search for must be in the first column of the given range that you specify in the table_array argument.
  • 2) table_array (Required) – The range of cells needs to be two or more columns of data. The range should contain the lookup_value and return value.
  • 3) col_index_num (Required) – The column number in the given range contains the return value. The counting starts from the leftmost column (i.e., the first column is 1) in the table array.
  • 4) range_lookup (Optional) – A logical value either TRUE or FALSE.
    • TRUE (1) – Approximate match: It looks for the closest value in the first column with the look_up value by assuming the first column in the table is sorted either alphabetically or numerically. It is the default match if you don’t specify this argument.
    • FALSE (0) – Exact match: – It looks for the exact match in the first Column with the look_up value.
  • Refer to the below screenshots to understand the arguments in pictorial representation.

First Argument:

https://img.particlenews.com/image.php?url=3MeLgu_0dVnDqWn00
First argumentMicrosoft Excel

Second Argument:

https://img.particlenews.com/image.php?url=4d6knu_0dVnDqWn00
Second argumentMicrosoft Excel

Third Argument:

https://img.particlenews.com/image.php?url=2rpLwy_0dVnDqWn00
Third argumentMicrosoft Excel

Fourth Argument:

https://img.particlenews.com/image.php?url=4WlJr2_0dVnDqWn00
Fourth ArgumentMicrosoft Excel

1) VLOOKUP – Looks only Right:

  • VLOOKUP function can only lookup right.
  • The value you want to look up must be in the first column of the given cell range.
  • The result values that you want to retrieve can appear in any column to the right of the lookup values.

Example 1:

https://img.particlenews.com/image.php?url=290tXS_0dVnDqWn00
Looks Right – Example 1Microsoft Excel
  • In this example, we have looked up a value ‘Robert’ in the “First Name” column.
  • Here, the column index argument is 2, so as per the cell reference, the VLOOKUP function looks suitable from the leftmost column (First Name) and returns the output as ‘John’ from the 2nd column (Last Name).

Example 2:

https://img.particlenews.com/image.php?url=0cc6tA_0dVnDqWn00
Looks Right – Example 2Microsoft Excel
  • In this example, we have looked up a value ‘Betty’ in the “Last Name” column.
  • Here, the column index argument is 3, so as per the cell reference, the VLOOKUP function looks suitable from the leftmost column (Last Name) and returns the output as ‘Texas’ from the 3rd column (State).

2) VLOOKUP – Column Numbers:

  • When using the VLOOKUP function, assume that every column is numbered starting from the left towards the right.
  • If you want to get an output from a specific column, you must provide the relevant number as the column index argument.
  • For example, if you want to retrieve output from the column that is second from the lookup column, then you have to give the column index argument as 2.

Example 1:

https://img.particlenews.com/image.php?url=4KtP2k_0dVnDqWn00
Column Numbers – Example 1Microsoft Excel
  • Here, in this example, we have looked up a value ‘1105’ in the “ID” column and retrieved the value from the “Last Name” column that is 3rd from the “ID” column.
  • As the “Last Name” column is 3rd from the lookup column, we have given the column index as 3.
  • The function returned the output as ‘Linda’ corresponding to the lookup value ‘1105’.

Example 2:

https://img.particlenews.com/image.php?url=0IbTEc_0dVnDqWn00
Column Numbers – Example 2Microsoft Excel
  • Here, in this example, we have looked up a value ‘Kenneth’ in the “Last Name” column and retrieved the value from the “Salary” column that is 2nd from the “Last Name” column.
  • As the “Salary” column is 2nd from the lookup column, we have given the column index as 2.
  • The function returned the output as ‘34808’ corresponding to the lookup value ‘Kenneth.’

3) VLOOKUP – Case-insensitive:

  • The VLOOKUP function performs a case-insensitive lookup within a table or cell range.

Example:

  • We are going to look up the value ‘SUSAN’ in the “First Name” column.
  • If the lookup value is present inside the table, we will retrieve the value from the “Last Name” column that is 2nd from the “First Name” column as a result.
https://img.particlenews.com/image.php?url=1nVscL_0dVnDqWn00
Case-insensitiveMicrosoft Excel
  • The VLOOKUP function is case-insensitive. In the “First Name” column, it looks up Susan, SUSAN, SuSan, Susan, etc., in the “First Name” column.
  • The function returned the output as ‘Ava’ as it locates the Susan (first occurrence) in the third row, so the SUSAN value is ignored.

VLOOKUP TRUE vs FALSE:

4) Exact Match (FALSE):

  • The VLOOKUP function looks up for the exact match in the table’s first column (leftmost column) or cell range if the range_lookup argument is given as FALSE.
  • You can also give zero (0) instead of FALSE.
  • If the function can’t find the exact match in the first column, it returns a #N/A Error.

Example:

  • The function looks for an exact match with the lookup value ‘1108’ in the given cell range as the argument is declared as FALSE.
https://img.particlenews.com/image.php?url=3Nl3G6_0dVnDqWn00
Exact match – formulaMicrosoft Excel
  • If the value 1108 is present within the cell range, the function returns the value in the 4th column corresponding to the same row of the lookup_value.
https://img.particlenews.com/image.php?url=4D0wAy_0dVnDqWn00
Exact match – outputMicrosoft Excel
  • So, for this example, it returned the output as ‘56126’.

5) Approximate Match (TRUE):

  • Sometimes, you may not require the exact match, and in that case, you can use the TRUE argument, so the function looks up the closest match.
  • If you set the argument as TRUE or omitted, it will search for an exact match first, and if an exact match is not found, it looks for the next highest value that is less than the lookup value.
  • You can also give one (1) instead of TRUE.
  • By default, it will take the argument as TRUE if you omit the argument.
  • If the lookup value is smaller than the smallest in the lookup range, the Excel VLOOKUP function will return the #N/A error.

Example:

  • In this example, the VLOOKUP function looks up for the value 1120 in the table’s first column (leftmost column).
  • Although the value 1120 is not present at the table, it will not throw any error as we have given the argument as TRUE (approximate match).
https://img.particlenews.com/image.php?url=2o3aMA_0dVnDqWn00
Approximate match – formulaMicrosoft Excel
  • There is no value 1120 in the first column, so it tells the function to return an approximate match.
https://img.particlenews.com/image.php?url=2Bqxup_0dVnDqWn00
Approximate match – OutputMicrosoft Excel
  • So, in this example, the value will be 1110 (the next highest value less than the lookup value), and it returns the salary output as ‘175689’ (4th column) of the same row.

6) First Match:

  • If the lookup column or leftmost column contains identical or duplicate values, the function considers the first occurrence and returns its corresponding row output.

Example:

  • Take a look at the example below. The VLOOKUP function is configured to find the salary for ‘William.’
https://img.particlenews.com/image.php?url=08Nn9E_0dVnDqWn00
First matchMicrosoft Excel
  • There are two entries with the first name William, so the function returns the salary of the first entry, 85256, and ignores the second instance.

7) Wildcard Match:

  • If you want to perform a partial match using the Excel VLOOKUP function, you have to use wildcards.
  • In simple words, you can give a part of a value that you want to search for when you don’t remember the exact value.
  • To use wildcards, you have to specify the last argument as FALSE for exact match mode.
  • To match any sequence of characters, you have to use Asterisk(*).
  • Use a Question mark (?) to match any single character.

Using Asterisk (*):

Example 1: Searching for a value that starts with certain characters

  • In this example, we have looked up a value that starts with ‘Kev’ in the “First Name” column.
https://img.particlenews.com/image.php?url=3No2Io_0dVnDqWn00
Wildcard Match – starts withMicrosoft Excel
  • The function found the ‘Kevin’ value in the First Name column, returning the state as ‘Michigan.’

Example 2: Searching for a value that ends with certain characters

  • In this example, we have looked up a value that ends with ‘es’ in the “Last Name” column.
https://img.particlenews.com/image.php?url=2wXvkb_0dVnDqWn00
Wildcard Match – ends withMicrosoft Excel
  • The function found the ‘James’ value in the Last Name column, which returns the salary as ‘85256’.

Example 3: Searching for a value that starts and ends with certain characters:

  • In this example, we have looked up a value that begins with ‘jet and ends with ‘ca’ in the “Last Name” column.
https://img.particlenews.com/image.php?url=1QT5Eb_0dVnDqWn00
Wildcard Match – starts and ends withMicrosoft Excel
  • The function found the value ‘Jessica’ in the Last Name column, returning the state as ‘Arizona.’

Example 4: Searching based on cell value

  • You can also enter the known part of the value in some cells, say I3, and combine the wildcard character with the cell reference using an ampersand (&).
  • In this example, we have looked up a value that starts with ‘Rob’ in the “First Name” column.
  • We entered the known part of the first name value in cell I3 to refer it to the formula.
https://img.particlenews.com/image.php?url=15TfaU_0dVnDqWn00
Wildcard Match – Based on cell valueMicrosoft Excel
  • The function found ‘Robert’ in the First Name column, so it returns the salary as ‘91345’.

Using Question Mark (?):

  • If you don’t know even what characters are present inside the table, but you know the number of characters, you can use the question mark instead of an asterisk to search for a specific number of characters.

Example: To search for a four-character value

  • In this example, we looked for a four-character value in the “First Name” column.
https://img.particlenews.com/image.php?url=2AXR0X_0dVnDqWn00
Wildcard Match – using a question markMicrosoft Excel
  • In the First Name column, “Noah” is the four-character value, so the corresponding row salary ‘70855’ is returned as output.

8) VLOOKUP – Two-way Lookup:

  • Generally, in the Excel VLOOKUP function, you have to give a static number for the column index argument.
  • But, you can also create a dynamic column index with the use of the MATCH function. This sort of technique assists you in matching both rows and columns by creating a dynamic two-way lookup.

Example:

  • In this example, VLOOKUP is configured to perform a lookup based on ID and Salary.
  • For the column index argument, we have used the MATCH function to get the position of the column “Salary.”
https://img.particlenews.com/image.php?url=1gy4We_0dVnDqWn00
Two-way lookupMicrosoft Excel
  • We have looked up a value ‘1107’ in the “ID” column and retrieved the salary ‘23456’ as output.

9) VLOOKUP – Lookup a Value from Another Sheet:

  • It is possible to look up a value from the table on another sheet by using the Excel VLOOKUP function.
  • To refer to a table on another sheet, you must add the sheet name and an exclamation mark before the table/cell range.

Example:

  • Refer to the below screenshot that shows the Sheet9.
https://img.particlenews.com/image.php?url=41ugXY_0dVnDqWn00
Table referred in Sheet9Microsoft Excel
  • In Sheet10, we have entered the VLOOKUP formula to search for the ‘1106’ ID in Sheet9.
https://img.particlenews.com/image.php?url=0YoAgb_0dVnDqWn00
Retrieves the value from Sheet9 to Sheet10Microsoft Excel
  • ID ‘1106’ is present in the Sheet9, and so the function retrieved the “First Name” of its corresponding row from Sheet9, ‘Sarah,’ and displays the output in Sheet10.

10) VLOOKUP – Lookup a Value from Another Workbook:

  • The VLOOKUP function can also look up a value from the Table, which is on another workbook.
  • To do so, you have to put the workbook’s name enclosed in square brackets before the sheet’s name.

Example:

  • Refer to the below screenshot that shows the external workbook named Table, and it contains a sheet named Sheet1 with a table.
https://img.particlenews.com/image.php?url=3txjI6_0dVnDqWn00
Table (External Workbook)Microsoft Excel
  • In the ‘VLOOKUP’ workbook, we have entered the VLOOKUP formula to search for the ‘1109’ ID in the ‘Table’ workbook.
  • You have to enter the location of the workbook with its name and sheet name within the single quotes before the exclamatory symbol in the formula.
https://img.particlenews.com/image.php?url=2cIfgQ_0dVnDqWn00
Retrieves the value from an external workbookMicrosoft Excel
  • The ID ‘1109’ is present in the external workbook (‘Table’), and so the function retrieved the “Last Name” of its corresponding row from the workbook, ‘Kenneth,’ and displays the output in the ‘VLOOKUP’ workbook.

11) INDEX and MATCH:

  • To perform advanced lookups, you can use INDEX and MATCH functions instead of using VLOOKUP.

Example:

  • Using the Match function, we have searched for an exact match with ID ‘1104’ within the given cell range.
https://img.particlenews.com/image.php?url=1O5vub_0dVnDqWn00
INDEX and MATCH – FormulaMicrosoft Excel
  • If the ID is present within the cell range, it will retrieve the State of its corresponding row, and for that purpose, we have used the Index function.
https://img.particlenews.com/image.php?url=2KiIqW_0dVnDqWn00
INDEX and MATCH – OutputMicrosoft Excel
  • It retrieved the state ‘Washington’ as output for the ID ‘1104’.

Common Errors:

#N/A error – If the range_lookup is FALSE and can’t able to locate the exact match within the cell range, the VLOOKUP function returns this error.
#REF Error– You will get this error value if the col_index_num is greater than the number of columns.
#Value Error– This error will be returned if the table_array is less than 1.
#Spill Error– If the formula depends on the implicit intersection for the lookup value and uses a whole column as a reference, you will acquire this error value.
#Name Error– To search for a person’s name, you have to use quotes around the name in the formula, or else you will get this error.
You may get the wrong result if range_lookup is TRUE or omitted. The first column needs to be sorted numerically or alphabetically. You will not get the expected return value if the first column is not sorted. Use either FALSE for an exact match or sort the first column.

Closure:

Searching for data in a table or cell range is straightforward with the use of the VLOOKUP function. We hope that the above article assisted you in learning all about the Excel VLOOKUP function in Office 365. The description, syntax, and diverse scenarios of the VLOOKUP function are explained distinctly for your easy understanding.

Comments / 0

Published by

I identify and develop opportunities. I'm a nice fun and friendly person, I'm honest and punctual, I work well in a team but also on my own as I like to set myself goals which I will achieve. I'm a Tech Freak & Excel as a Tech Writer.

New York State
134 followers

More from Jenny

Comments / 0