Excel Formula Help - Nested VLOOKUPS
In Microsoft Excel, we would sometimes like to take certain contents from one table and run them against another in order to determine a 3rd output. In the scenario below, we have an sales employee table above a list of sales made. The only common factor in each table is the employee ID, which we can use to find details of an employees, name, phone number and email, in relation to a sale number. The space at the top is where we will create our formulas.
Firstly, we should simply be able to search the bottom table with the sale number to determine the employee ID. This straight forward lookup follows the usual pattern, =VLOOKUP(lookup _ table, table _ array, col _ index _ number, [range _ lookup])
So above, we have A3 for the lookup _ value and the selected table _ array as can be seen selected with a green border. From that selected table we need column 3, the col _ index _ num, to retrieve the ID and we also need to tell the formula to work with exact matches, so we add FALSE to the end and close off the brackets; then hit return.
As we can see, the employee ID has been returned. However, we would actulally like the employee name and not their ID number. In which case, we will simply use the ID as the first lookup _ value in a new VLOOKUP formula. To do this we take the VLOOKUP formula we have created and use it as the lookup _ value for our new VLOOKUP formula. We start with =VLOOKUP(VLOOKUP(A3,A14:C16,3,FALSE), as we can see, the original VLOOKUP is highlighted as the lookup _ value for our new formula:
We finish off the new formula in the same way as the old one by selecting the table _ array and the col _ index _ num we are asking for within that table, and finally determining if we are asking for an exact or approximate match. Our final formula should read, =VLOOKUP(VLOOKUP(A3,A14:C16,3,FALSE),A8:D10,2,FALSE) as shown here:
Any hey presto, we find that sale 2 was completed by Jim Smith:
We can test that everything works by entering other values in sales column to make sure the correct results are returned.
Furthermore, we need to have the Sales Number entry return other employee details including Phone and Email. To do this we only need to change the col _ index _ num entry in the main VLOOKUP formula. For example, phone details are in column 3:
And email details are in column 4:
Again, let's test to see that we are getting consistent results:
Everything looks good.
For more help and advice for working with multiple tables and data sets, why not give the Excel4Business experts a call?
