Vlookup and Absolute Cell Reference

We learnt in the earlier training videos how to use Vlookup, Vlookup with named ranges and Vlookup with lists. Now we’ll learn how to use Vlookup using a range that we select manually. Let’s see how we can implement this process of Vlookup using absolute cell reference correctly:

  • We use the same vlookup table array that we also used earlier and the table is shown below:
Average Marks Grade
0 Work Harder
50 D
60 C
70 B
80 A
  • In the sheet1 in cell H5 we enter the formula: =Vlookup(F5, B3:C7,2,TRUE)
  • F5 is the lookup value, B3:C7 range is the table array with the average marks and the corresponding grade, 2 is the column C containing the grades and TRUE represents the range_lookup, a boolean value. Had we not written TRUE the function would have worked in this scenarion because the data in column B in sheet3 is sorted in ascending order
  • When we press enter the function gives us the correct grade
  • Now if we an autofill the function doesn’t work correctly. This happens due the use of relative reference by autofill and the table array becomes B4:C8 from B3:C7, B5:C9, B6:C10 and finally B7:C11. Now we know that our data range is only from B3:C7
  • Therefore it becomes important to fix this range. Earlier the named range fixed the table array.
  • Now if don’t wish to name the range we can use the absolute cell reference method by using $B$3:$C$7
  • Now when we do an autofill after using the Vlookup function once, the cell references don’t change. They remain B3:C7 due to absolute referencing
  • Our results now display the earlier and correct values for the grades

Using Vlookup with absolute cell reference of the range is not easy to implement if your table array is large. It’s wiser to use named ranges.
View the Excel training video:

Further reading: VLOOKUP: What it is, and when to use it

Leave a Reply

Your email address will not be published. Required fields are marked *