Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table, index, array, match, lookup?
_K____L____ _M ____N ____O ____P ............. 1____100___200_ __300 __400 ___500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value of 299 in H3 i wish to compare to L1:P1. The correct column is M. With that established, I now want to compare a value of 19490 in I3 to the numbers in column M. I wish to return the row number. In this case, row 3. How can I accomplish this? I have tried several suggestions. None have worked. -- spxer ------------------------------------------------------------------------ spxer's Profile: http://www.excelforum.com/member.php...o&userid=37025 View this thread: http://www.excelforum.com/showthread...hreadid=569606 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table, index, array, match, lookup?
Hi spxer, For your example do you want the result to be 3 or M3? For the former =MATCH(I3,INDEX(L1:P4,0,MATCH(H3,L1:P1))) or the latter =ADDRESS(MATCH(I3,INDEX(L1:P4,0,MATCH(H3,L1:P1))), MATCH(H3,L1:P1)+COLUMN(L1)-1,4) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=569606 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
table, index, array, match, lookup?
spxer wrote...
_K____L____M ____N ____O ____P ............. 1____100___200___300 __ 400 ___500 2____19000_19000_19000_19000_19000 3____19240_19480_19720_19960_20200 4____19600_20200_20800_21400_22000 I have a value of 299 in H3 i wish to compare to L1:P1. The correct column is M. With that established, I now want to compare a value of 19490 in I3 to the numbers in column M. I wish to return the row number. In this case, row 3. How can I accomplish this? I have tried several suggestions. None have worked. Don't start new threads. Follow-up in the threads you've already begun. Given the specs above, =MATCH(I3,INDEX(K1:O4,0,MATCH(H3,K1:O1))) returns 3, and FTHOI, =LOOKUP(I3,INDEX(K1:O4,0,MATCH(H3,K1:O1))) returns 19480. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Table Array in Lookup Function | Excel Worksheet Functions | |||
Lookup nearest value (Index & Match) | Excel Worksheet Functions | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |