lookup

paldersley

New Member
Joined
Sep 13, 2006
Messages
1
simple lookups are limited to either rows or columns, i want to find a particular value which may be anywhere within a defined range

i have tried offset and match, but i always need to know where the target is for these to work

eg where is 3

a b c e
1 0 0 0 0
2 1 0 3 1
3 0 1 2 0
4 5 0 0 0

i need formula to give me C2 which i can then use in a GO TO command, or anything that does the same thing
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=INDEX(B1:E1,MATCH(G3,INDEX(B2:E5,MIN(IF(B2:E5=G3,ROW(B2:E5)-ROW(B1))),0),0))&INDEX(A2:A5,MIN(IF(B2:E5=G3,ROW(B2:E5)-ROW(B1))))

confirmed with Ctrl + Shift + Enter.
Book1
ABCDEFGH
1abce
210000
3210313c2
430120
545000
6
Sheet3
 

Watch MrExcel Video

Forum statistics

Threads
1,113,791
Messages
5,544,302
Members
410,601
Latest member
Silver2
Top