Go Back

Using VLOOKUP with Multiple Lookup Tables: Best Practices

While working in Excel, we often need to get values from multiple tables. This is possible by using VLOOKUP and IF functions. In this tutorial we will learn how to pull values from several tables, using VLOOKUP function with multiple lookup tables.

Figure 1. Final result

Syntax of the VLOOKUP formula with 2 tables

First, we will use the IF function to determine which table we will reference in our lookup. Then, we will nest the IF function inside the VLOOKUP function to create a dynamic lookup formula.

The generic formula to lookup from 2 tables looks like:

=VLOOKUP(lookup_value, IF(logical_test, table1, table2), 2, 0)

The parameters of the IF function are:

  • logical_test – a logical test which will decide which table to use
  • [value_if_true] – if a logical condition is true, table1 will be a result of IF function
  • [value_if_false] – if a logical condition is false, table2 will be a result of IF function.

The parameters of the VLOOKUP function are:

  • lookup_value – a value which we want to find in a lookup table
  • table_array: IF(logical_test, table1, table2) – a table in which we want to look up. The table will be a result of IF function, depending on a condition
  • col_index_num – a column number in lookup table which value we want to pull
  • range_lookup – default value 0. This means that we want to find an exact match for a lookup value.

Setting up Your Data

Figure 2. Tables for VLOOKUP

We will now look at the example to explain in details how this function works. Above all, let’s start with examining the structure of the data that we will use.

The first table consists of 3 columns:

  • The first column is “Product” (Column B)
  • The second one is “Salesman” (Column C),
  • While the third one is “Sales” (Column D).

The second and third tables have data about Mike’s and Ann’s sales per product.:

  • Columns F and G have products and sales for Mike,
  • Columns I and J have products and sales for Ann.

As a result, we want to get sales value in the first table (column D), depending on salesman and product.

Get Product Sales Value from Salesman

Figure 3. Using VLOOKUP with multiple lookup tables

The formula looks like:

=VLOOKUP(B3,IF(C3 = "Mike",$F$3:$G$4,$I$3:$J$4),2,0)

In our example, lookup_value is “Product” column (B3 cell), while the table_array depends on “Salesman” column value. Therefore, if the salesman is Mike, we will look up in the second table (F3:G4).

On the other hand, if the salesman is not Mike, we will lookup in the third table (I3:J4). Furthermore, the IF function will return a table_array parameter for VLOOKUP function. Col_index_num has value 2, as we want to pull value from the second column of the range. Finally, range_lookup has value 0, because we want to find an exact match of “Product” values.

To use VLOOKUP with 2 tables, we need to follow these steps:

  • Select cell D3 and click on it
  • Insert the formula: =VLOOKUP(B3,IF(C3 = "Mike",$F$3:$G$4,$I$3:$J$4),2,0)
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

As a result, we will get sales $230 in the cell D3. The value of “Salesman” in C3 is Mike, so the IF function will return first table (F3:G4) as a lookup table. Finally, the VLOOKUP will return $230, as the product is Product A in the cell B3.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

replace numbers with multiple using vlookup
Solved by X. F. in 11 mins
I need help with a VLOOKUP formula to lookup multiple criteria from another worksheet
Solved by K. H. in 27 mins
Using vlookup for tables across time periods
Solved by V. H. in 18 mins

Leave a Comment