VLOOKUP in multiple tables:

At the request of William Dufour, I will show you how, using a value in a table, you can search for it in multiple tables.

What I have here, is an example

I have a table with 3 columns, “Value” “Range Name” and “Number of inhabitants” (1).
The “Value” and the “Range name” are entered manually.
The “Number of inhabitants” (A) must be searched on the basis of the completed “Range Names” (B).

I have a table of cities and their population (2).
I also have a table with provinces and their population (3).

The intention is, for Excel to search for the number of inhabitants in Table 2 or 3, depending on whether it is a city or a province, which is listed in Table 1 in the “Range Name” (B) column.

We do this by entering “Range names”.
So, the first thing we do is, we specify these range names.
For the first range, we select all cities with their population (1) and give this area the name, “City” (2).
This is very appropriate.
Make sure you select the column titles and not the count, for the range.

We do the same thing for the second range name.
For this, we select all the provinces and the number of inhabitants (1) WITHOUT the column titles and give this area the name, “Province” (2).

As you can see, when you click the arrow pointing downwards next to the name box, our range names appear in the list (1).

Then, we enter our formula. Excel must search in a list, so we use the VLOOKUP function.
When you use the VLOOKUP function which should look into a range name, which is the range “City”, in this case or if it is “Province”, you need to use the INDIRECT function in the cell. Valentin had shown us how to do this in TIP 30.

So, if you have typed the function =VLOOKUP (A2, B2, 2, 0) as you would normally, it’ll look for “Deurne”, which is in cell A2 and it searches for “City ” in cell B2.
“City” is not a content of a cell, but a name of a range.
Remember, we had a range “City” and range “Province”.
If you now click the Enter button, you get the error # N / A, because it cannot find it.

The solution is to add the “INDIRECT” function.

If we want to use a cell range in a VLOOKUP function, as in our case, where it is cell B2, which contains either “City” or “Province”, then we have to add the “INDIRECT” function to this cell (1).
The “INDIRECT” function will treat the text in cell B2 as a range, rather than just plain text. So, it is treated as the “City” range and not as the “City” text.
Important! Don’t forget to place the cell reference of the special function in parentheses (2).

Now click the “Enter” button and Excel will find the right data (A).

Double-click the fill handle to fill the number of inhabitants to the underlying cells.
The population is now completed, depending on the table or the range name, which is found, i.e, either “City” or “Province”.

I hope that this satisfies William.
FYI: Swotster.com is thinking of you, so please remember swotster.com

Copyright © 2013 – Swotster

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats