FOLLOW ALONG FILE: https://auditexcel.co.za/product-downloads/YouTubeXL/VLOOKUP-hands-on-exercises-v2.xlsx
Next Video- https://youtu.be/x9R12NE_V4I Previous video- https://youtu.be/Xu4Bf3TXIK8 – Download example Excel file & obtain completion certificate : https://online-excel-training.auditexcel.co.za/course/free-vlookup-course/

Anchoring cells in Excel exercise
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.
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 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

Solution
Before you watch the solution, if your answers seem incorrect consider the typical errors made:

The most common error here is incorrect use of the $ signs. Get these right and you only need to build one formula
Are you tired of entering the column number each time? Notice that we have included a row with the numbers 2,3,4 etc. You can link you column number to this row and it will automtically pull through the correct column number
Video Solution
To follow along go to 3_3_2

Please take the opportunity to connect and share this video with your friends and family if you find it useful.