Vlookup formula from Multiple Tables

by on September 8, 2017

Vlookup formula from Multiple Tables

We have learned Vlookup formula in case of a single table. Now its turn to retrieve data with the help of Vlookup formula from Multiple Tables. We will be using a combination of vlookup and other functions to get the job done. In excel, there is more than one option to arrive at the same calculation. Our aim is to look up for the sales commission rate for different products based on the number of units sold. watch it closely to grasp the idea.

 Vlookup formula from Multiple Tables

IF and Vlookup Combination method

In column D, we will use the combination of IF and vlookup formula to collect data from the tables. Click in cell D4 and enter the first part of the formula. Since we are extracting data from multiple tables, we will enter the multiple IF formula.

=IF(B4=$G$3,$G$5:$H$10,IF(B4=$J$3,$J$5:$K$10,IF(B4=$M$3,$M$5:$N$10)))

This part of the formula simple shows that IF the condition met then return the data from the connected table. In simple terms, if B4=ProductA(G3) or ProductB(J3) or ProductC(M3) then returns data from the relevant table. As we will be copying the formula to other rows, we locked the criteria and the tables by placing $ signs. You can use the F4 key to lock the table or cells. After copying the first part the calculation will look like the one below. Don’t worry, we still have not defined the lookup criteria through the vlookup formula, once we add it, the concept will be cleared to all of you.

Vlookup formula from Multiple Tables

Let’s add the second/vlookup part of the formula. The C4 which represents the unit sold is lookup criteria and 2 after the comma at the end of the formula is the column number from which the formula will extract data.
=VLOOKUP(C4,IF(B4=$G$3,$G$5:$H$10,IF(B4=$J$3,$J$5:$K$10,IF(B4=$M$3,$M$5:$N$10))),2)

Copy the formula down the rows and there you have your calculation. If your tables are dynamic, means you are adding more data to it then kindly use the format as Table and your formula will collect new data as soon as you add it to the tables.

Vlookup formula from Multiple Tables

Indirect and Vlookup Combination method

In this combination of formula, the second part is the same. In order for the indirect function to work, we will use name manager function. Select the ProductA data tables from G5:H10 and give it a name as shown in the example below. Hit enter once you enter the name in the box. In exactly the same way, enter the names for the ProductB and ProductC.

 Vlookup formula from Multiple Tables

The next step of defining the names for the data tables is to insert the Indirect function. The formula for the indirect function will be in Cell E4 =INDIRECT(B4). Copy the formula down the rows and the picture will look like this. Again, we still to add the vlookup part of the formula.

Vlookup formula from Multiple Tables
Add the vlookup part of the function below and your formula is now complete.

=VLOOKUP(C4,INDIRECT(B4),2)

Vlookup formula from Multiple Tables

The first combination of the formula is lengthy compared to the second one. If you have data with very few formula then the indrect function is the way to go about it. In case you have large data with formulas everywhere in the data then the IF function is better. Indirect function recalculates every time you enter anything or hit the enter button. It makes calculation process slow. So the choice depends on what type of data you are working on. It’s alway great to extract data with the help of Vlookup formula from Multiple Tables.

Hit the shares buttons to share it with your friends.

 

 

Please follow and like us:
0

Leave a Reply

Your email address will not be published. Required fields are marked *