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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,118,889
Messages
5,574,846
Members
412,620
Latest member
sharma7s
Top