HLookup and Row Index Number

[TKrepitch]

Hello. I am currently using the following formula, but want to
improve it:

=HLOOKUP(C3,'Drivers'!$C$4:$CT$34,5)

Currently it is retrieving the number in the fifth row, but I am
concerned that the number will change if the reference worksheet is
ever altered.

Is there a way to have Excel look up a value that is in a row with a
certain label (eg, "Average Size") instead of the fifth row? Thanks!

 

[Niek Otten]

You can, using the MATCH() function on the row with the labels. Look in HELP
for details.
But of course it would have been better if Excel could do it the way you
describe, at least as an option.

 

[Anders S]

Not sure exactly what you want, but

=HLOOKUP(C3,myRange,ROW(myRange)+ROWS(myRange)-1)

will pick the value in the last row of a named range.

Otherwise,

=ROW(myCell) also works as the row parameter in HLOOKUP.

HTH
Anders Silvén