hlookup row_index_num

N

neda5

I am going through a file that someone else has created and stumbled upon the following formula

HLOOKUP(MAX(B5:K5),D5:$Q$97,ROW($C$97)-ROW(A5)+1,FALSE)

Can anyone tell me what the ROW($C$97)-ROW(A5)+1 part would normally do.
I understand that the +1 would increment the row by one on each iteration, but what would be the effect of the row-row part.
Your comments are greatly appreciated.
Thanks. Neda
 
B

Biff

Hi Neda!

That is the reference to which row in the table array to
search for a match of the maximum value found in the range
B5:K5.

ROW($C$97)-ROW(A5)+1 evaluates to 97-5+1 = 93.

It looks like the ROW() functions are being used as a
means to increment the reference to the search row. You'll
notice that ROW($C$97) is an absolute reference while ROW
(A5) is relative. When drag copied, ROW(A5) will increment
accordingly.

Biff
-----Original Message-----
I am going through a file that someone else has created
Click to expand...
and stumbled upon the following formula
HLOOKUP(MAX(B5:K5),D5:$Q$97,ROW($C$97)-ROW(A5)+1,FALSE)

Can anyone tell me what the ROW($C$97)-ROW(A5)+1 part would normally do.
I understand that the +1 would increment the row by one
Click to expand...
on each iteration, but what would be the effect of the row-
row part.
 
Top