0 of 23 lessons complete (0%)
VLOOKUP Exact Match

# Anchoring cells in Excel exercise

This is a preview lesson

A useful feature of VLOOKUP is its ability to anchor cells. A common problem is that data appears in different cells at different times (each time you download a report a key cost item you are looking for appears on a different line). You can use VLOOKUP to find the cost item and make it appear on a set row. You can then build ratios and graphs off this static row

In the file you downloaded look for the sheet name ‘VLOOKUP- Anchor info-1a’

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.
• Now copy the yellow block (D46 to J51) and paste it into the relevant section on the next sheet (VLOOKUP Ex4b- 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

## Video Instructions

To follow along go to 3_3_1