lookup not in row but in matrix

[frens]

Hello,

Horizontal Lookup tries to find a value in a single ROW and Vertica
Lookup tries to find a value a single COLUMN.

I want to find a value in a RANGE of rows and columns.
E.G. I want to find the value X in the range consisting of rows 2,3,4,
and the columns C,D,E,F.

---A B C D E F
1
2
3------------X
4
5
6--p-e--s k f g

In this examle I want to find X in cel E3 and return the correspondin
value in row 6, that is "f".

The Lookup functions I know of can only find X in (say) row 1 an
return the correspondinfg value in (say) row 6.


anyone

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+sHIFT+ENTER):
=INDEX(A6:F6,MIN(IF(C2:F5="X",COLUMN(C2:F5))))

 

[Alan Beban]

Hello,

Horizontal Lookup tries to find a value in a single ROW and Vertical
Lookup tries to find a value a single COLUMN.

I want to find a value in a RANGE of rows and columns.
E.G. I want to find the value X in the range consisting of rows 2,3,4,5
and the columns C,D,E,F.

---A B C D E F
1
2
3------------X
4
5
6--p-e--s k f g

In this examle I want to find X in cel E3 and return the corresponding
value in row 6, that is "f".

The Lookup functions I know of can only find X in (say) row 1 and
return the correspondinfg value in (say) row 6.


anyone?

If the functions in the freelydownloadable file at
http://home.pacbell.net/beban are available to your workbook

=INDEX(C2:F6,5,INDEX(ArrayMatch("X",C2:F5),2))

Alan Beban