VLOOKUP Examples
VLOOKUP Ex1
In this example we want to join the address list of our students with a list of students who have not paid their fees to determine who has money outstanding. We also want to see whether our address list is complete by checking if everyone who owes money 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.
VLOOKUP Ex2
In this example we have 2 lists which contain similar information. The problem is that each list contains some unique items and we need to know which items are unique in each list.
- Using VLOOKUP, in column G, create a formula that checks whether the transaction ID in column B, exists in the transaction ID of List 2 (column L)
- Again using VLOOKUP, in column Q check whether the transaction ID in column L exists in List 1 (column B)
VLOOKUP Ex3a & B
In this example you receive regular reports on the cash flows for a project. However, each month and each project can have varying amounts of data. You want to do a simple calculation of the total receipts to the total payments (D55 row). Ideally you want to get the key lines of information to pull through to the same row each time so that your ratio will work each time.
- Use VLOOKUP in cell D46 to pull through the amount for Period 1 (column D) for the cell in C46 (Total Opening Bank Balance). Build the formula so that it is easier to copy and paste elsewhere. Also make sure it includes the database up to the red line as this is the maximum amount of data we expect.
- Finish the rest of the yellow block.
- As an alternate to entering the column number each time into the VLOOKUP formula, try linking to the numbers in row 45. This saves time in setting up big sheets and is easy to change.
- Compare your answers in D55 row to the answers in D57 row
- Now copy the yellow block (D46 to J51) and paste it into the relevant section on the next sheet (VLOOKUP Exb- Anchor Info).
- Note that the data in the blue cells is on different lines but your formula should correctly pull through the information.
- To check compare the answers in Row 55 to row 57
VLOOKUP Ex4
Simple question.
Why are the VLOOKUP’s in B14 and C14 giving an error message when student A111 clearly exists in the table?
To see the full course contents click here.