Joining tables with the corresponding values

There are two tables. The first contains list of products with Product ID. The second is the actual quantity of products in the stock. Our task is to assign correct Product Name to Product ID in the second table.

Solution

As you see, items in the column Product ID are not in the same order. Therefore we can’t just simple copy the values. We will use VLOOKUP function to find the correct Name for the Product ID. VLOOKUP function belongs to the Lookup&Reference category.

So, select cell F2 and write this formula:

=VLOOKUP(D2,$A$2:$B$10,2,0)

or  you can fill this dialog window:

The first column of argument Table_array must be the column with values, that we search. (Sometimes is necessary to change column order to achieve this.)

$ character in the second argument is important, because when you copy this formula, Table_array must be still the same. VLOOKUP would not work correctly without the $ character in the second argument.

Col_index_num is 2, because column with Product Name is on the second place in the first table (in the Table_array).

Range_lookup is 0, because we want to find exact match. Here you can also write the word FALSE. In some of the other examples, I will show you when to use Range_lookup=1.

Now, copy value from cell F2 to other cells.

I deliberately put into the table the Product ID that is not in the list of products (the first table).  It’s product A015. VLOOKUP function show #N/A error value, that means: item is not available, because it doesn’t exist.

Video tutorial: