< Go Back

VLOOKUP with 2 lookup tables

What is the VLOOKUP function?

As the name implies, the VLOOKUP is a function that can be used to lookup and recover data from a certain column in a table that is organized into vertical rows. The “V” stands for “Vertical”. This function supports exact matching, approximate matching and even wildcards (*?) for partial matches.

Formula

=VLOOKUP(value,table,col_index,[range_lookup])

Explanation

  • Value – is the value to search for in the first column of a table.
  • Table – the table from which to get a value from.
  • Col_index – the column from which the value would be gotten from.
  • Range_lookup – this is optional. Use “TRUE” for an approximate match, “FALSE” for an exact match.

VLOOKUP with 2 lookup tables

The formula above applies to just 1 lookup. When you need to work with 2 lookup tables, you have to use the IF function inside VLOOKUP to control which of the table is used.

The Generic formula used in this case is:

=VLOOKUP(value,IF(test,table1,table2),col,match)

Example

How to VLOOKUP with 2 lookup tables

In this example, we would be giving employees their salaries based on their ages and shoe sizes.

The following steps should be taken;

  1. Open your spreadsheet with the 2 tables you will be working with OR open a new spreadsheet and create the 2 tables.

Figure 1. Sample sheet for VLOOKUP with 2 lookup tables

Note: We are trying to assign salaries to employees based on shoe size and age. Table 1 gives the recommended salary for shoe size for employees below 25 years while Table 2 gives the recommended salary for shoe size for employees 25 years and above.

  1. Click on the cell to be filled (John’s salary cell) John is 23 years old & his shoe size is 42.

Figure 2. Lets pay John

  1. Insert this formula =VLOOKUP(C5,IF(B5<25,Table1,Table2),2,TRUE)) in the formula bar (“fx” box) or in the cell itself.

Figure 3. Insert this formula

  1. Press Enter. Now John has been paid $600!

Figure 4. John has been paid

  1. For Steven, use this formula =VLOOKUP(C6,IF(B6 < 25,Table1,Table2),2,TRUE) and press Enter.

Figure 5. Steven has also been paid

  1. For Stacy, use this formula =VLOOKUP(C7,IF(B7 < 25,Table1,Table2),2,TRUE) and press Enter.

Figure 6. Stacy has also been paid

Note

The IF function in this formula worked from inside out and is entered as the “table_array” argument in VLOOKUP. It runs a logical test on the value in column B “Shoe size”, which represents the shoe size of an employee in the company. For John, If B5 is less than 25 (Employee less than 25 years old), then table1 is returned as the value if true. If B5 is greater than 25 (employee older than 25), table2 is returned as the value if false.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar