We learnt in the
erlier 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.