Problem with HLookup. Not sure if it is a bug or not
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. |
Problem with HLookup. Not sure if it is a bug or not
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" wrote: I have a table as set up 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. |
Problem with HLookup. Not sure if it is a bug or not
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" wrote: 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" wrote: I have a table as set up 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. |
Problem with HLookup. Not sure if it is a bug or not
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? --- "rockycho912" wrote: 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" wrote: 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? |
All times are GMT +1. The time now is 11:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com