Combine tables using the VLOOKUP function

We use the VLOOKUP function to combine/merge 2 tables which use the same keyword. This could be useful in many ways, so it is an important function to remember.

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

[lookup_value] = Input character or number that would be the base for search.
[table_array] = Select the range of the table to search for.
[col_index_num] = Select column number (counting from the left) of the information wished to be displayed.
[range_lookup] = Not obligatory. To find data which matches the [lookup_value] completely, input ‘FALSE’. To find data matching the [lookup_value] partially, input ‘TRUE’. If nothing is written here, it will automatically be ‘TRUE’.

Definition of Function A function to extract necessary information [col_index_num] through a [lookup_value] from a specified [table_array].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

When there is a column of the same keyword in 2 or more different tables, the VLOOKUP function allows us to combine the 2 tables into 1.

<Image of combining tables>

Example of use 1) Combine (merge) tables/VLOOKUP function

The left table below shows the daily sales based on product ID, and the right shows the product ID information. We will combine the tables to create 1 table with all the information including the ‘Number purchased’, ‘Product’ name, and ‘Unit price ($)’.

 

First of all, we add the column ‘Product’ and ‘Unit price ($)’ in the left table.

 

We will then use the VLOOKUP function so that the information for the newly created column D and E is properly displayed.


① Use the VLOOKUP function to extract ‘Product’ name based from the ‘Product ID’.

(1) [lookup_value] = Select cell which would be the base for search, cell ‘C4’ of the ‘Product ID’.
(2) [table_array] = Select cell range [H4:J13] from the right table (List of product IDs).
(3) [col_index_num] = The information we need from the right table is the ‘Product’ name (located in the 2nd column from the left), so we input ‘2’.
(4) [range_lookup] = We want information that completely matches, so we input ‘FALSE’.
=VLOOKUP(C4,H4:J13,2,FALSE)

② Use the VLOOKUP function to extract ‘Unit price’ from the ‘Product ID’.

(1) [lookup_value] = Select cell which would be the base for search, cell ‘C4’ of the ‘Product ID’.
(2) [table_array] = Select cell range [H4:J13] from the right table (List of product IDs).
(3) [col_index_num] = The information we need from the right table is the ‘Unit price’ (located in the 3rd column from the left), so we input ‘3’.
(4) [range_lookup] = We want information that completely matches, so we input ‘FALSE’.
=VLOOKUP(C4,H4:J13,3,FALSE)

 

Example of use 2) Combine tables that are in a different sheet

We can also combine tables that are in a different sheet like below.

 

Use the VLOOKUP function to extract the ‘Product’ name and ‘Unit price’ from the ‘Product ID’.


How to select information from a different sheet

In order to combine tables from different sheets using the VLOOKUP function, we include the ‘Sheet name’ +’!’ (Exclamation mark) before the cell location. (i.e. the [table_array])
For example, to select cell range [A1:C3] from the Product IDs sheet, we input the following.

‘Product IDs’!A1:C3

To combine/merge the information in the ‘Product IDs’ table with the ‘Daily sales’ table, we set up the following formula.

(1) Extracting the ‘Product’ name
=VLOOKUP(C4,‘Product IDs’!B4:D13,2,FALSE)
(2) Extracting the ‘Unit price’
=VLOOKUP(C4,‘Product IDs’!B4:D13,3,FALSE)

 

‘Complete match’ and ‘Partial match’ in VLOOKUP

The VLOOKUP function has two types of [range_lookup], ‘complete match (FALSE)’, and ‘partial match (TRUE)’.

About partial match(TRUE)

It will search for numerical values lower than, and closest to the [lookup_value] within the [table_array]. This only applies when the [lookup_value] is a numerical value.

 

Example of use) Using partial match = TRUE

The table below shows the results for an examination and is created to work out the grades based on the scores.
We will display the grades in column D based on the criteria in the right table.

In the above, the [lookup_value] is 98. It searches for a number less than and closest to 98, which results to the grade ‘A’. We will look at how to carry this out using the VLOOKUP function.

① Use the VLOOKUP function to extract ‘Grade’ from the ‘Score’.

(1) [lookup_value] = Select cell which would be the base for search, cell [C4].
(2) [table_array] = Select cell [F4:G8] from the the right table (Criteria).
(3) [col_index_num] = The information we need from the right table is the ‘Grade’ (located in the 2nd column from the left), so we input ‘2’.
(4) [range_lookup] = We want information that partially matches, so we input ‘TRUE’.
=VLOOKUP(C4,F4:G8,2,TRUE)

 

POINT!
In order to search using partial matching (TRUE), the column that applies to the [table_array] in the function must be in ‘ascending order‘(from smallest).
If it is not in ascending order, the results will not come out properly.

 

How to hide a VLOOKUP result of 0 (zero) and #N/A

In situations like below, the VLOOKUP function will display a ‘0 (zero)’ or a ‘#N/A(error)’.

 

① When the result of search is an empty cell → ‘0’

When searching for the product ID ‘B003’ from the table on the right, a ‘0’ appears as the product name is missing.

 

② When the [lookup_value] (the cell which would be the base for search) is empty. → ‘#N/A’

Because the ‘Product ID’  which serves as the base for search (i.e. [lookup_value]) is missing in the table below, an error ‘#N/A’ appears.

 

We can use the IF function to hide ‘0’ or ‘#N/A’. ⇒ Click here to know how to use the IF function

 

How to hide ‘0’) IF function

① If the result of VLOOKUP is ‘0’, hide using the IF function.

(1) Input the IF function. → ‘=IF(‘
(2) The information in the IF function would be, “If the result of the VLOOKUP is a ‘0’, display an empty space, “” (double quotations), and if not, display the result of the VLOOKUP“.
VLOOKUP(C4,I4:K13,2,FALSE)=0,“”,VLOOKUP(C4,I4:K13,2,FALSE)
(3) Complete the rest of the IF function ‘)’.

=IF( VLOOKUP(C4,I4:K13,2,FALSE)=0,””,VLOOKUP(C4,I4:K13,2,FALSE) )

How to hide the ‘#N/A’) Combination of ISERROR and IF function

To hide the error ‘#N/A’, we can use the following function.

ISERROR(value)

[value] = Choose the subject to error decision.

Definition of function A function that extracts TRUE if the [value] is #N/A, and if not, FALSE
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

① If the result of VLOOKUP is ‘#N/A’, hide using the ISERROR and IF function.

(1) Input the IF function. → ‘=IF(‘
(2) The information in the IF function would be, “If the VLOOKUP (C5,I4:K13,2,FALSE) result is ‘#N/A’, display an empty space, “” (double quotations)、and if not, display the result of the VLOOKUP“.
→「ISERROR(VLOOKUP(C5,I4:K13,2,FALSE)),“”,VLOOKUP(C5,I4:K13,2,FALSE)
(3) Complete the rest of the IF function ‘)’.

=IF( ISERROR(VLOOKUP(C5,I4:K13,2,FALSE)), “”,VLOOKUP(C5,I4:K13,2,FALSE) )

How to hide the ‘#N/A’) Eliminate the errors using the IF function

① If the [lookup_value] is empty, display an empty space in the result (of the VLOOKUP) using the IF function.

(1) Input the IF function. → ‘=IF(‘
(2) The information on the IF function would be, “If cell C5 is empty, display an empty space, “”(double quotations), and if not, display the result of the VLOOKUP“.
→「C5=””,“”,VLOOKUP(C5,I4:K13,2,FALSE)
(3) Complete the rest of the IF function ‘)’.

=IF( C5=””,””,VLOOKUP(C5,I4:K13,2,FALSE) )

 

Use the VLOOKUP function with multiple conditions

The following table on the left shows the sales price in a secondhand car shop.
We added a table on the right which extracts the sales price automatically if we select the ‘Manufacturer’ and ‘Name’ (of car).
In this case, we will use the VLOOKUP function to set up a formula based on 2 [lookup_values], cell G4 and H4.

 

Example of use) Using VLOOKUP function with multiple conditions

Normally, we can only select 1 [lookup_value] in VLOOKUP. Therefore, we add a column used ‘For search’, which would be the result of the 2 factors combined together.

Below, we add the result of column C and D combined/merged together.

Cell E4 example → =C4&D4

 

Set up the VLOOKUP funcion in cell J4.

① Extract the ‘Sales price’ from the ‘Manufacturer’ and ‘Name’ using the VLOOKUP function.

(1) [lookup_value] = Select the cells ‘Manufacturer’ and ‘Name’ which are the bases of search by combining them using ‘&’ as follows. → H4&I4
(2) [table_array] = Select cell range [E4:F11] from the left table (Secondhand cars:sales price list).
(3) [col_index_num] = The information we need from the left table is the ‘Price’ (located in the 2nd column from the left), so we input ‘2’.
(4) [range_lookup] = We want information that completely matches, so we input ‘0’ which is the same as ‘FALSE’.
=VLOOKUP(H4&I4,E4:F11,2,0)

 

POINT!

There are other ways to input the [range_lookup] other than ‘TRUE’ and ‘FALSE’, as described below.

・ ‘FALSE’ = ‘0’
・ ‘TRUE’ = A numerical value other than ‘0’