A common requirement in Excel is to join databases with VLOOKUP. This often happens when there is a common item in the databases e.g. a client number. You may need to use this when you want to:
- show product codes and names
- show a client number and client name if they are on different lists
VLOOKUP Join Databases
In the file you downloaded look for the sheet name ‘VLOOKUP Join-1’
In this example we want to join the address list of the employees with a list of outstanding expenses to determine who has money outstanding. We also want to see whether our address list is complete by checking if everyone is on the address list.
- In cell C8 use VLOOKUP to link the address list (Column A to C) to the outstanding bills (columns G to L) list and pull through the total outstanding bill.
- In L8, use a VLOOKUP to check whether all the students listed as having money outstanding are shown on the Address List.
To follow along go to 3_1_1
Before you watch the solution, if your answers seem incorrect consider the typical errors made:
- You $ signs are wrong. When you copied the formula down the dollar signs are often a problem. If you struggle with $ signs make sure you do the Excel $ signs lessons.
- You didn’t include the whole table as a table array.
To follow along go to 3_1_2