Nested vlookup in excel : vlookp trick

Nested VLOOKUP is helpful when you have to retrieve the details from two table and the lookup value is not the common field in both the table. You may know nested IF which is helpful if you are checking multiple condition in same formula. Similarly you can use VLOOKUP formula within VLOOKUP to get lookup value. This can be explained with below example.

In this example, in one table the data consists of Employee Number, Tax Account No, Name and Salary details, in the second table, the Tax Account No. and tax amount. From these two table, you can bring both Salary amount and Tax amount in new table based on Employee Number with the help of nested VLOOKUP.

Nested vlookup

You may note that the employee number is available in one table but it is not available in second table. But both tables have Tax Account No. as common Field. Therefore, Nested VLOOKUP can be applied to get details from both table as below:

NESTED VLOOKUP
=VLOOKUP(VLOOKUP(B18,$A$4:$B$13,2,0),$F$4:$G$13,2,0)

To get the tax amount in VLOOKUP table, first it will check for Tax Account No. in Table One for employee number. Based on the Tax Account No. it will search tax amount in second table.

This is helpful when you have data in more than one table and the lookup value is not available in all tables.

Leave a Reply

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