Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP last match in a table
In Excel, let's say I have the following in cells A1:B5:
ABC, ABC, ABC, XYZ, XYZ 12, 34, 56, 78, 90 I'm trying to create a formula that will find the last "ABC" in the table, then return the number beneath it (in this case, 56). If I use HLOOKUP("ABC", A1:B5, 2, TRUE), it seems to work, while if I change the last paramter to FALSE it finds the first match. But the final parameter for HLOOKUP -- boolean Range_Lookup -- is supposed to be whether it's looking for an exact match or closest match. It doesn't seem to have anything to do with finding the first or last match, so while using it seems to work, it makes me nervous because I don't understand why it works. Is there a better way to do this or can someone tell me why using TRUE instead of FALSE for the final parameter finds the last match? I'm using Excel 2000. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP last match in a table
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLOOKUP last match in a table
This will work entered with ctrl + shift & enter
=INDEX(A1:E5,2,MAX((INDEX(A1:E5,1,)="ABC")*COLUMN( A1:E5))) of course if you know you always will get the result from secon row there is no need for INDEX =INDEX(A2:E2,MAX((A1:E1="ABC")*COLUMN(A1:E1))) -- Regards, Peo Sjoblom wrote in message oups.com... In Excel, let's say I have the following in cells A1:B5: ABC, ABC, ABC, XYZ, XYZ 12, 34, 56, 78, 90 I'm trying to create a formula that will find the last "ABC" in the table, then return the number beneath it (in this case, 56). If I use HLOOKUP("ABC", A1:B5, 2, TRUE), it seems to work, while if I change the last paramter to FALSE it finds the first match. But the final parameter for HLOOKUP -- boolean Range_Lookup -- is supposed to be whether it's looking for an exact match or closest match. It doesn't seem to have anything to do with finding the first or last match, so while using it seems to work, it makes me nervous because I don't understand why it works. Is there a better way to do this or can someone tell me why using TRUE instead of FALSE for the final parameter finds the last match? I'm using Excel 2000. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Match function in a two input table | Excel Worksheet Functions | |||
Trying to match a text string to a data table, any suggestions? | Excel Worksheet Functions | |||
Need help with HLOOKUP and MATCH functions | Excel Worksheet Functions | |||
Index & Match on Pivot Table | Excel Discussion (Misc queries) |