 # How to use VLOOKUP Function in Excel | allows 03 Best Matches

By

The VLOOKUP function is a lookup and reference function that accepts a value that looks the value up in a vertical lookup table with data organized in columns and returns a result. The VLOOKUP function is one of the most popular Excel lookup functions.

More specifically, the VLOOKUP  function in EXCEL signifies ‘vertical lookup‘ (The ‘V’ stands for ‘Vertical’). The VLOOKUP function is an Advanced Excel function searches for a value in the first column (more specifically, lookup column where lookup value exists) of the lookup table and look up to the right and returns the corresponding value in the same row of a specified table column.

The lookup table is arranged in vertical columns (which explains the ‘V’ in the function’s name). Each column is used for a new record.

VLOOKUP supports approximate match (indicated by ‘TRUE’ or numerical ‘1’), exact match (indicated by ‘FALSE’ or numerical ‘0’) and wildcard (such as asterisk ‘*‘, question mark?) for partial matches.

This Tutorial Contents:

# I. How to use the VLOOKUP Function in Excel

The VLOOKUP function is used in Advance Excel in different ways:

1. Using the VLOOKUP function to Find an Exact Match
2. Using the VLOOKUP function to Find an Approximate Match
3. Using the VLOOKUP function to Find a Partial Match
4. The VLOOKUP function performing a ‘Left LOOKUP’ or ‘Reverse LOOKUP’
5. The VLOOKUP function performing a ‘Double VLOOKUP’ or ‘Nested VLOOKUP’ or ‘IFERROR VLOOKUP’
6. The VLOOKUP function performed with multiple criteria
7. The VLOOKUP function performed with multiple columns
8. The VLOOKUP function performed with multiple sheets
9. The VLOOKUP function performed with Pivot Table
10. The VLOOKUP function nested with COUNTIFS finds the duplicate entry
11. The VLOOKUP function nested with the IF function performed logical VLOOKUP

## Syntax of VLOOKUP Function: ## Arguments of VLOOKUP Function:

The VLOOKUP function’s arguments are as follows:

• lookup_value [Required Argument]

The value to be looked up in the first column of the lookup table (i.e., more specifically is called the lookup column) is called lookup_value.

For example, if the lookup_value is located in the cell C3 then our range should be started with column C; if the lookup_value present in any cell of D column (like D3, D4, D7, etc.), then the range should be started with column D.

Lookup_value can be either a value (number, date or text) or a cell reference (a reference to a cell containing a lookup value), or the value returned by some other Excel function. For example:

(i) Lookup for number: =VLOOKUP(90, B2:C12, 2,0) – the formula will search for the number 90. Please note that we don’t use ‘double quotes’ for number values.

(ii) Lookup for text: =VLOOKUP(CAN-1, C3:F13, 4,0)– the formula will search for the text ‘CAN-1‘. Please note that we always enclose the text in ‘double quotes’.

(iii) Lookup for values in another cell: =VLOOKUP(N5, C3:F13, 4,0) – the formula will search for the value in cell N5 i.e. ‘CAN-1’. N5 is the cell address of ‘CAN-1’. This is called cell reference.

• table_array [Required Argument]

The table_array is the range (one or more columns of data) that contains the lookup table. It is basically the rectangular grid of cells that contains all the data we’re searching for retrieving a value.

The VLOOKUP function searches in the leftmost column (i.e., the first column is called the lookup column) of the table array. Please keep in mind that, first column/lookup_column starts from where lookup value exists.

The VLOOKUP function retrieves the value from the column in the table_array is called the result column. Lookup column and the result column may be the same (when selecting the single column) or different.

Thus, the formula =VLOOKUP(‘CAN-1’, C3:F13, 4, 0) will search for ‘CAN-1’ in a range of cells C3 to F13 because C is the first column or lookup column of the table_array and F is the result column.

The table array may contain various values such as text, dates, numbers, or logical values. Values are case-insensitive, which means that uppercase and lowercase text are treated as identical.

Thus, the formula we can write also =VLOOKUP(can-1, C3:F13, 4, 0). The lowercase ‘can-1’ is symmetrical of the upper case ‘CAN-1’. The VLOOKUP formula works and retrieves the same value right to the ‘CAN-1’.

When the VLOOKUP formula performs in the same Excel workbook, the table array is in the relative cell reference (no dollar \$ sign is there); whereas the VLOOKUP formula performs in the different Excel workbook, the table array by default makes an absolute cell reference (one dollar \$ sign before the column address and another  dollar \$ sign before the row address).

• col_index_num [Required Argument]

This is an integer /positive value. The col_index_num is the column number within the table_array from which the matching value is returned.

The leftmost column is the first column in the table_array defines as 1, the second column is 2, the third column is 3, and so on.

If we want to retrieve the ‘COUNTRY’ name of the F column, then the formula is written as =VLOOKUP(‘CAN-1’, C3:F13, 4, 0). The formula will search for ‘CAN-1’ in cells C3 to F13 and returns a value from column F in the same row (because F is the 4th  column in the specified table array).

Thus, the column_index_number will be 4.

• range_lookup [Optional Argument]

The range_lookup determines whether to search for an exact match (FALSE or numeric 0) or approximate match (TRUE or Omitted or numeric 1). This is an optional argument, but very important.

• TRUE or 1 or omitted– the lookup function will return an approximate match. If an exact match is not found, the function will return the next largest value that is less than lookup_value. If the range_lookup is true, the first column of the VLOOKUP table must be sorted in ascending/alphabetical order; otherwise, the function will return the incorrect result.
• FALSE or 0 –  the lookup function will return an exact match. IF the VLOOKUP function can’t find an exact match, the function returns #N/A error. Remember that if the lookup_value is a text string, we can use wildcard characters (* and ?), but make sure that range_lookup is set to FALSE or 0.

As we search for the exact match, so the last argument in the formula will be either numeric zero (0) or FALSE. So the formula to be written like  =VLOOKUP(‘CAN-1’, C3:F13, 4, 0)

Finally, we press Enter to accept the formula and Excel will close the last parenthesis by default.

Thus, we get the result COUNTRY name ‘CANADA‘ against lookup_value ‘CAN-1‘.

# II. Using the VLOOKUP function to Find an Exact Match

Step to Start:

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

• When lookup_value is a text: =VLOOKUP(CAN-1‘, C3:F13, 4, 0)

• To make the formula more dynamic by using cell reference: =VLOOKUP(N5, C3:F13, 4, 0)

• Step 1: A place (a blank cell) is required to VLOOKUP formula

Select the cell to get the result of the VLOOKUP function (i.e., O5); generally, it is the place right side of the lookup_value in the same row. In the given example, cell O5 is the place right side of the lookup_value located in cell N5.

In this cell, press equality ‘=‘ sign to start formula and just type a few letters ‘=vlo….‘ and select the VLOOKUP function from the given suggestion list with the help of a down arrow (↓), if required. Then press the ‘Tab’ key, VLOOKUP syntax appears with the open parenthesis.

Please note that the syntax in an upper or lower case does not matter, EXCEL by default considers as upper case. • Step 2: Placing the first argument Lookup_value

It is the first argument of the VLOOKUP function is the  lookup_value where we select a text or cell reference. It is also called the ‘Field of the starting point‘.

In the given example, ‘CAN-1’ is a text and is located in cell N5, which means the cell reference of ‘CAN-1’ is N5. We can write the formula in two ways:

(i) Either as a text and to be written in a double quotation mark (‘ ‘) and place a comma (,) just after which indicates the closing of the first argument.

=VLOOKUP(‘CAN-1’,

(ii) Or, as a cell reference and place a comma (,) just after which considers the closing of the first argument. Please note that any double quotation mark does not require in case of a numeric value or cell reference.

=VLOOKUP(N5,

⇒ Note:

• We always prefer to use a dynamic VLOOKUP formula rather than manual changing so that we do not require to change the formula every time after making any changes in the condition of lookup_value. Due to this reason, we always prefer using a cell reference.

• Additionally, we make the VLOOKUP formula more dynamic by fixing the cell references.

• The position of lookup_value may be left, right, upward and downward; but it doesn’t matter with the VLOOKUP formula which means formula works perfectly in any lookup position.

• We place a comma (,) after the lookup_value to move to the next argument. Please remember that placing a comma after any arguments in any formula indicates to Excel that the current argument placement has been completed and move to the next argument.

• Step 3: Placing the second argument table_array

The second argument of the VLOOKUP function is table_array, which is the range (one or more columns of data) that contains the lookup table from where the VLOOKUP formula retrieves the data. It is also called the ‘information table.

The table_array can be located in the same (or existing) workbook or another workbook.

A table_array should have a lookup column (i.e., the first column) and the result column (i.e., the column from where the data or the value retrieved). Lookup column and the result column may be the same (when selecting the single column) or different.

In the given example, C3:F13 is the table_array

We would like to retrieve the data ‘COUNTRY’ name from the table_array in respect to the lookup value ‘CAN-1’.

‘CAN-1’ is found in the lookup column ‘C’ and ‘COUNTRY’ name is found in the result column ‘F’. Then the table_array will be C3:F13. Generally, we consider the Subject heading in the table array.

• C3 – table_array starts from column ‘C’ and row address is ‘3’ (first cell of the table).

• F13 – table_array ends in column ‘F’ and row address is ’13’ (last cell of the table).

So, we can write the formula as:

=VLOOKUP(‘CAN-1’C3:F13,

Or, =VLOOKUP(N5, C3:F13,

• ⇒ Note:

(i) The position of the table_array is important. The position of the lookup_value is always in the left column and it should be the first column of the table_array. Whereas the position of the retrieved value is always located on the right side.

For example, ‘CAN-1’ is the lookup_value and is located in the column ‘C‘. So the table_array starts from here. ‘COUNTRY’ name is the retrieved value and is located on the right side, i.e., in the column ‘F‘. Thus, the table_array will be the C3:F13.

(ii) The VLOOKUP function doesn’t work at all if the lookup_value (i.e., ‘CAN-1’) is located on the right side and the result column from where retrieves the value (i.e., the ‘COUNTRY’ name) is on the left side.

Because the VLOOKUP function doesn’t perform the reverse lookup, it always looks right and it is the biggest limitation of this function.

(iii) In the given example, we cannot consider the column ‘A‘ (i.e., Sl No) and the column ‘B‘ (i.e., Dialing Code (ISO)) in the table_array because the lookup_value ‘CAN-1’ is not present in both the columns.

(iv) After placing the table_array argument, we put a comma (‘,’) after that which indicates to move to the next argument.

• Step 4: Placing the third argument col_index_num

The third argument is col_index_num. It is the total count of columns between the lookup column and the result column in a table_array. It is always an integer/numerical value. In the given example, the total count of columns between C and F is 4. So, 4 is the column_index_number.

After selecting the range C3:F13, the formula shows the selected range denomination like 11R x 4C.

• 11R – 11 rows are selected in the table_array range. ‘R’ stands for ‘ROWS’. The count of rows considers in the HLOOKUP function.
• 4C – 4 columns are selected in the table_array range. ‘C’ stands for ‘COLUMNS’. The count of columns considers in the VLOOKUP function.

So we consider the col_index_number in the VLOOKUP formula as 4 and we can write the formula as

=VLOOKUP(‘CAN-1’C3:F13, 4,

Or, =VLOOKUP(N5, C3:F13, 4,

• Step 5: Placing the last argument range_lookup

The last argument of the VLOOKUP function is the range_lookup. Most of the cases, the VLOOKUP function is used for an exact match (‘FALSE‘ or numerical ‘0), but it is also used for the approximate match (‘TRUE‘ or numerical ‘1 or omitted).

If the lookup_value is a text string, then we always go for the exact match. In the given example, ‘CAN-1’ is the lookup_value and it is the text. So we always search for an exact match i.e. FALSE or 0.

Thus, we can write the formula in two ways:

=VLOOKUP(‘CAN-1’C3:F13, 4, 0)  or, =VLOOKUP(‘CAN-1’C3:F13, 4, FALSE)

=VLOOKUP(N5C3:F13, 4, 0)  or, =VLOOKUP(N5C3:F13, 4, FALSE)

• Step 6: Press Enter to accept the formula

Finally, press Enter to accept the formula. Excel considers that all arguments in the formula are properly mentioned, the formula ends immediately and the last parenthesis closes by default.

# III. Features & Limitations of the VLOOKUP Function

## (A) Feature 1: Right Lookup

The VLOOKUP function always looks for a value in the leftmost column of a table_array (lookup column) and returns the corresponding value from the right column (Result Column).

## (B) Feature 2: First Match

If the lookup_value (e.g., ‘CAN-1’) in the table array contains duplicates, the VLOOKUP function match only the first instances. The phenomenon of the VLOOKUP function is called the first match.

In the given example, ‘CAN-1’ has duplicated value in the table array.  If we want to get the project cost of both the same instances, we only get the same value for both cases.

## (C) Feature 3: Case-insensitive

The VLOOKUP function is case insensitive, which means that the Uppercase and the Lowercase characters are treated as equivalent.

N.B.: INDEX, MATCH, and EXACT function altogether to perform a case-sensitive lookup.

## (D) Feature 4: VLOOKUP formula returns #REF! Error & #N/A Error

• #REF! Error

If we get a #REF! Error, first we recheck the formula and correct it. Generally, if we put the col_index_num more than the selected table_array, it returns a #REF! Error.

For example, we are selecting the table_array C2:G12 which means the count of columns in between the range is 5. So in this case, if we put column_index_num as 6 instead of 5 showing a #REF! Error.

• #N/A Error

If we get a #N/A Error, even the formula has written correct, then check the following parameters:

• If there is an extra space after the lookup_value, it returns a #N/A Error. For example, there is an extra space after ‘CAN-1’.
• If there is an extra space before the lookup_value, it returns a #N/A error.
• If the lookup_value is in a text format, it returns a #N/A error. So, in that case, we should turn them into a general format.
• If the VLOOKUP function cannot find a match, it returns a #N/A error. This error can be modified with any kind of message or the formula by the IFERROR function. Figure: The lookup_value cannot find a match in the lookup column
• Like lookup_value, if the lookup column is in text format, the VLOOKUP function also returns a #N/A Error. We need to convert the text format into the general format in 3 ways:

(a) by using the Convert Text to Column Wizard

(b) by using the Format Cells window using excel shortcut Ctrl+1

(c) using excel shortcut Alt+H+N and type General.

• If there are extra spaces that exist in the lookup column, the VLOOKUP function also returns #N/A error.  We should remove these extra spaces in two ways:

(a)  by using the ‘Convert Text to Column Wizard’

(b) by using the TRIM, SUBSTITUTE, and CHAR combined formula. Figure: there are extra spaces that exist in the lookup column

## (E) Feature 5: VLOOKUP formula breaks after inserting/deleting a column in a Table Array

01. What will happen after inserting a column within the table_array? After inserting a column anywhere in the table_array, the retrieved value is also changed. For example, a column inserts between Column ‘D’ & ‘E’ within the table_array, then the retrieved value shifted one column before.

However, after inserting a column (using Excel shortcut CTR++), the col_index_num 5 indicates the column ‘COUNTRY’ name instead of ‘Project Cost (in Dollar)’. As a result, the VLOOKUP function retrieves the ‘COUNTRY’ name instead of ‘Project Cost (in Dollar)’.

02. What will happen after deleting a column within the table_array?

After deleting a column anywhere in the range (i.e., the table_array), the VLOOKUP function returns a #REF! Error, because deleting a column, the range becomes smaller but the column number remains the same as before. So, in this scenario, the col_index_num will be more than the table_array.

For example, before deleting a column, the range was C3:G13 and col_index_num were 5. After the deletion of a column, the range becomes smaller C3:F13 and the col_index_num remain the same as 5 as before.  But it should be 4 instead of 5.  As a result, a #REF! Error returns. ## (F) Feature 6: VLOOKUP formula works while referencing to Another Workbook

If the VLOOKUP formula refers to another workbook, once we close the workbook having a main dataset /table array, then the VLOOKUP formula works perfectly, but it displays the full path of the location of reference workbook. There are two workbooks. The lookup_value is placed in the workbook ‘VLOOKUP-1’ and the table_array (main database) is in the workbook ‘VLOOKUP-2’. The VLOOKUP formula in workbook ‘VLOOKUP-1’ reference to the workbook ‘VLOOKUP-2’. After closing the reference workbook ‘VLOOKUP-2’, we will find that the VLOOKUP formula works perfectly in the VLOOKUP-1 workbook, but it displays the full path of the location of that workbook.

Before closing the VLOOKUP-2 workbook:

After closing the VLOOKUP-2 workbook

## (G) Feature 7: Extend the table_array till the end of the range

When we start working with the VLOOKUP function, we must extend the table_array range till the end of the range. It is very much helpful to Lookup with the multiple columns or simply ‘Multiple VLOOKUP‘. In this case, we should change the col_index_number and get the result.

As a result, we do not require to apply the multiple times of VLOOKUP formula and manage time a lot for preparing the data analysis.

In the given example, extend the table_array till the end of the range. Put the number 4 as col_index_num to get the ‘Country’ name. After completing the VLOOKUP formula, fix the cell reference. Then copy (Ctrl+C) the formula to the right side. In this formula, change the col_index_num by 5 to get the ‘Project Cost’. Figure: Benefits of extending the table_array until the end of the range. Only change the col_index_num manually.

Equivalently, we use the MATCH() function in place of col_index_num which will make the dynamic formula. The MATCH function returns the relative position of a cell in a range that matches a specified value. So we don’t need to change the col_index_num manually. Figure: Benefits of extending the table_array until the end of the range. The MATCH() function handles the col_index_num dynamically.

# IV. Using the VLOOKUP function to Find an Approximate Match

Like an exact match, we can use the VLOOKUP function to approximate match, i.e., the range_lookup should set to ‘TRUE‘ (the default setting) or omitted or numeric ‘1. Generally, the approximate match function is applied where the lookup value is in number format rather than text format.

• =VLOOKUP(G3, B3:D11, 3, 1) – numeric 1 is used in range_lookup to get the approximate match; we get the result A-.

• =VLOOKUP(G5, B3:D11, 3, TRUE)TRUE can be used in range_lookup instead of numeric 1; we get the same result A-.

• =VLOOKUP(G7, B3:D11, 3) range_lookup can be omitted, the VLOOKUP function will allow a non-exact match; we get result A-.

Before applying an approximate match function, the first thing we need to do is sorting the first column of the table_array in ascending order. This is very important because the formula will stop searching as soon as it finds the nearest match smaller than lookup_value. If the data does not sort in ascending order, we will get incorrect or unexpected results or the #N/A error.

• Why do we get the result A- but not A?

Our lookup value is 84 and its closest values are 80 and 90 in the table. The formula returns A- rather than A, which means the VLOOKUP function retrieves the value against 80 instead of 90. Because the VLOOKUP formula with an approximate match retrieves the closest value that is less than the lookup value. The scale of marks against 80 is A-.  Therefore, we get the result A-, not A.

# V. Using the VLOOKUP function to Find a Partial Match (using the Wildcard characters)

We can use the following wildcard characters in the VLOOKUP function for the partial match of lookup_value:

• Question mark (?) allows us to match any single character; and

• Asterisk sign (*) allows us to match any sequence of characters.

Wildcard characters are used in many cases:

(i) When we cannot remember the exact text looking for in the lookup_value.

(ii) When we want to find some word(s) or character(s) which are present in the cell’s contents.

• =VLOOKUP(‘CA*‘,C3:C12,1,0) – find the name starting with ‘CA’. The result is CAN-1.

• =VLOOKUP(‘*91′,C2:C12,1,0) – find the name ending with ’91’. The result is IND-91.

• =VLOOKUP(‘U*1′,C2:C12,1,0) – find the name starting with ‘U’ and ending with ‘1’. The result is USA-1.

• =VLOOKUP(‘?????‘,C2:C12,1,0) – find the 5-character name. The result is CAN-1. It is the first 5-character name in range C2:C12.

If we have only a few characters of the lookup_value, then we can get the result easily with the help of the VLOOKUP formula and the wildcard characters. Please notice that we use an ampersand (&) sign before and after a cell reference to concatenate a text string.

The position of characters may be in the middle, first or last it does not matter at all, we can get the result! Just follow the given figure.

# Conclusion

There is an important list of things to remember about the VLOOKUP function:

(01) The VLOOKUP function always looks right and it is the biggest limitation of this function.

(02) If the lookup column contains duplicate values, The VLOOKUP formula will match the first value only.

(03) The VLOOKUP function is case-insensitive, which means that uppercase and lowercase characters are treated as equivalent or identical.

(04) The VLOOKUP function is frequently used for exact matches (FALSE or zero ‘0‘) but it is less used for the non-exact or approximate matches (TRUE or omitted or ‘1‘).

(05) If the range_lookup is allowed for an exact match (FALSE or 0), then do not require to sort the lookup column of the table.

But if the range_lookup is allowed for an approximate match or non-exact match (TRUE or 1 or omitted), then the values in the lookup column (i.e., the first column) must be sorted in ascending / alphabetical order. Otherwise, the VLOOKUP formula returns an incorrect or unexpected value.

(06) The VLOOKUP function with an approximate match retrieves the closest value that is less than the lookup value.

(07) If the lookup_value is a text string, we can use the wildcard characters (* and ?), but ensure that range_lookup is set to FALSE or 0.

(08) If there is an existing VLOOKUP formula in the worksheet, in that case, the formula breaks even after inserting /deleting a single column in the table. This is so because the column index value (i.e., the col_index_num) doesn’t change automatically, even after the column(s) inserted/deleted.

(09) If the col_index_num is less than 1, the VLOOKUP formula returns a #VALUE! Error.

Similarly, if the col_index_num is greater than the number of columns in the table_array, the formula returns a #REF! Error.

(10) The VLOOKUP formula works in either the same workbook or another workbook. Once we close the reference workbook (having a table or dataset), then the VLOOKUP formula works perfectly, but it will display the full path of the location of the reference workbook.

(11) Using a cell reference (dollar sign ‘\$’) within the VLOOKUP formula makes the dynamic formula, which means the formula is copied to another location, cell references change automatically.  Due to this, we do not require to apply the VLOOKUP formula multiply times.

(12) #N/A! Error – This error occurs while the VLOOKUP function fails to find a match to the lookup_value.

(i) If there is an extra space after or before the lookup_value, it returns a #N/A error.

(ii) If the lookup_value is in text format, it returns a #N/A error. We should convert it into its general format.

(iii) If the first column (lookup column) of the table_array contains numbers entered as text, the formula returns  #N/A! Error. Other than the first column in the table array may be in text format it does not matter, the VLOOKUP formula retrieves data from the table.

(iv) If the VLOOKUP function doesn’t find a match, it returns #N/A error. This error can be replaced with any kind of message or formula with the help of the IFERROR function.

(13) #REF! Error – Occurs if either:

(i) The col_index_num argument is greater than the number of columns in the table_array, or

(ii) The formula is attempted to reference cells that do not exist.

(14) #VALUE! Error – Occurs if either:

(i) The col_index_num argument is less than 1, it does not recognize as a numeric value; or

(ii) The range_lookup argument is not recognized as one of the logical values TRUE or FALSE.

(15) The VLOOKUP function allowing the use of wildcard, i.e., an asterisk mark  (*) or a question mark (?). While the asterisk mark is used for finding any number of characters, but each question mark is used for individual characters.

(i) =VLOOKUP(CA*,C3:C12,1,0) – find the name starting with ‘CA’. The result return: CAN-1.

(ii) =VLOOKUP(*91,C2:C12,1,0) – find the name ending with ’91’. The result return: IND-91.

(iii) =VLOOKUP(U*1,C2:C12,1,0) – find the name starting with ‘U’ and ending with ‘1’. The result return: USA-1.

(iv) =VLOOKUP(?????,C2:C12,1,0) – find the first 5-character name. The result return: CAN-1.

(16) Excel VLOOKUP function using fewer characters to give the name. Characters may be located in the first, middle or last of the name.  For example,

(i) =VLOOKUP(‘*’&’AN‘&’*’,C3:C12,1,0) – find the name having ‘AN’. The result return: CAN-1. Characters are located in the middle.

(ii) =VLOOKUP(‘*’&’IN‘&’*’,C3:C12,1,0) – find the name having ‘IN’. The result return: IND-91. Characters are located in the first.

(iii) =VLOOKUP(‘*’&’A-1‘&’*’,C3:C12,1,0) – find the name having ‘A-1’. The result return: USA-1. Characters are located in the last. • 03 Types of Excel Cell Reference: Relative, Absolute & Mixed

• 03 Best Ways: Excel REVERSE VLOOKUP | VLOOKUP to the left |

• 05 Best Ways: VLOOKUP Multiple Criteria in Excel

• 03 Best Ways: EXCEL DOUBLE VLOOKUP/ IFERROR VLOOKUP/ NESTED VLOOKUP 