Excel: Use VLOOKUP with Multiple Lookup Tables

---

You can use the following VLOOKUP formula in Excel to use multiple lookup tables:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

This particular formula attempts to look up the specific value in cell G2 in the first column of the range A2:B7 and return the corresponding value from the second column.

If the value in cell G2 cannot be found in the first lookup table, then the formula will try to look for it in the first column of the range D2:E7 and return the corresponding value from the second column of that range.

The following example shows how to use this formula in practice.

Example: Use VLOOKUP with Multiple Lookup Tables in Excel

Suppose we have two tables in Excel: One that contains the names of various Western conference basketball teams and another that contains the names of various Eastern conference teams:

Suppose we would like to look up the team name Kings in either table and return their corresponding points value.

We can specify the Kings as the team name to look up in cell G2 and then type the following formula into cell H2:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

The following screenshot shows how to use this formula in practice:

The formula returns 19, which is the correct points value that corresponds to the Kings.

If we change the name of the lookup team in cell G2, the formula will automatically update to return the correct points value.

For example, suppose we change the team name to Cavs:

How This Formula Works

Recall the formula that we used to look up the team name in cell G2 using multiple lookup tables:

=IFERROR(VLOOKUP(G2,A2:B7,2,0),VLOOKUP(G2,D2:E7,2,0))

Here is how this formula works:

First, we use VLOOKUP to attempt to look up the value in cell G2 (“Kings”) in the range A2:B7.

This will either return the second value from the range or #N/A.

Then we use the IFERROR function to check if the result from the first VLOOKUP returned #N/A or not.

If #N/A is returned, then we instead return the result from the second VLOOKUP function, which then looks up the value in cell G2 in the next range of D2:E7.