table, index, array, match, lookup?

S

spxer

_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.
 
D

daddylonglegs

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)
 
H

Harlan Grove

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.
Click to expand...

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.
 
Top