What would be faster - doing an exact match VLOOKUP in an entire column, e.g. VLOOKUP(A1, B:C, 2, 0) or creating a dynamic named range in columns B and C with a formula that searches the entire column to find the last used row, e.g.
=OFFSET($B$1, 0, 0, MATCH("*",$B:$B,-1), 2)
and then having each VLOOKUP search in this named range? Is the MATCH in the named range formula calculated each time for each VLOOKUP?
What if I were to have another cell, say D1 and set it equal to
=MATCH("*",$B:$B,-1)
then define the named range as
=OFFSET($B$1, 0, 0, $D$1, 2)
Would that be faster at all? If I am in a sheet set to manual calculation, would that even work since D1 needs to be calculated before the range is set?
Thanks.
=OFFSET($B$1, 0, 0, MATCH("*",$B:$B,-1), 2)
and then having each VLOOKUP search in this named range? Is the MATCH in the named range formula calculated each time for each VLOOKUP?
What if I were to have another cell, say D1 and set it equal to
=MATCH("*",$B:$B,-1)
then define the named range as
=OFFSET($B$1, 0, 0, $D$1, 2)
Would that be faster at all? If I am in a sheet set to manual calculation, would that even work since D1 needs to be calculated before the range is set?
Thanks.
