Problem with HLookup. Not sure if it is a bug or not

[rockycho912]

I have a table as setup below:

0 1 2 3 4 5 ... 59 60 (row A)
0 0.16 0.32 0.48 0.63 0.79 ... 9.34 9.50 (row B)
0 17 28 36 56 90 ... 20 0 (row C)

Row A is just a row of arbitrary number reference from 0 to 60
Row B is 1/60 divisions of a given length, in this case, 9.5m
Row C is results I generated from other formula. These values are not in
sequence or equal increments but always increase from 0 then peaks somewhere
in between then decrease again.

I'm trying to use"=HLookup(MAX(C1:C61),A1:C61,2,False)" to find the
particular point on the given length where the result was the greatest. I
understand with the "false" command the table_arrary doesn't necessary have
to be in order but all I am getting is #N/A as a result and it seems to me
this was down to a problem with the sorting of the table_array because when I
leave out the "false" command, meaning the table_array has to be in order, I
always get 9.5 (the final value) as my results.

I got around this by using the index/match as very well documented on this
forum, but I still want to know if I am doing anything wrong with the HLookup
function or is there really a bug or something.

 

[Bob Bridges]

I think you're right in your use of FALSE as the 4th argument. What I see is
another problem, or maybe two others. I use R1C1 notation myself, but it
seems to me you're confusing your rows and columns; you speak of rows A, B
and C, but usually in A1 notation A, B and C refer to columns and rows are
denoted as numbers. Could it be as simple as that? Seems to me your formula
ought to to refer not to MAX(C1:C61) but to MAX(A3:BI3), not A1:C61 but
A1:BI3. Or, if you're using R1C1, to MAX(R3C1:R3C61) and R1C1:R3C61.

If so, I see a further problem: HLOOKUP insists on the value you're
searching for - MAX(A3:BI3) - being in the first row of the table, not the
third. If you want to search on the max value in row 3, and return a
corresponding value from row 2, and don't want to rearrange the rows, you can
still do it; you just have to do a MATCH instead of HLOOKUP. Match gives you
the COLUMN NUMBER your max value appeared in - say 33 - which you can then
use with INDIRECT to pull the value in row 33 col 2.

How am I doing so far?

 

[rockycho912]

you've got me with your second problem. I missed the point where excel only
look up the value in the first row. thanx a million. my problem wasn't helped
by the fact that i was using a chinese version of office, which has all the
help files in chinese. even i am fluent in chinese, the guys at microsoft did
a pretty poor job in translating and i hadn't the slightest clue what they
were talking about in the help files. maybe if any microsoft staff reading
this: employ someone who can actually translate properly and proof read again
to see if it made any sense!

 

[Bob Bridges]

Happy to help. So what's the deal with you speaking of row A? Is that
something about the Chinese version, that they use letters for the rows and
numbers for the columns? Or is there some option in Excel that lets the user
reverse the two, maybe?