Match, index,....

[Guest]

I have a matrix in cells A1 across to D1 and A1 down to a4 (16 cells)

It looks like this:
A B C D
1 5 6 7
2 8 M N O
3 9 P Q R
4 10 S T U

If i enter "6" in cell m1, and "9" into cell m2, i want resultant intersect
"Q" to appear in m3

If i enter "7" in cell m1, and "10" into cell m2, i want resultant intersect
"U" to appear in m3

Thanks very much

 

[Guest]

Try something like this:

M3: =INDEX(A14,MATCH(M1,A11,0),MATCH(M2,A1:A4,0))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Ron

thanks, something still amiss...

5,8 comes out m (correctly)
6,9 comes out q (correctly)
7,10 comes out u (correctly)

5,9 comes out n (incorrect) should be p
5,10 comes out o (incorrect) should be s
6,8 comes out p (incorrect) should be n
6,10 comes out r (incorrect) should be t
7,8 comes out s (incorrect) should be o
7,9 comes out t (incorrect) should be r

???????????????

Also, what is the "0" for at the end of each match

Thanks very much

 

[Guest]

YIKES! I swapped the row and column references.....

Here you go:

M3: =INDEX(A14,MATCH(M2,A1:A4,0),MATCH(M1,A11,0))

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

thanks, this works, what is the "0" for at end of each match ?

 

[Guest]

The 3rd argument in the MATCH function indicates the Match_Type.
Zero indicates an Exact Match.

Check Excel Help on that function for the other options.

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

thanks

The 3rd argument in the MATCH function indicates the Match_Type.
Zero indicates an Exact Match.

Check Excel Help on that function for the other options.

***********
Regards,
Ron

XL2002, WinXP-Pro