lookup

[paldersley]

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

 

[Peter_SSs]

paldersley

Welcome to the Mr Excel board!

See if this thread is some use to you:
http://www.mrexcel.com/board2/viewtopic.php?t=199308

 

[fairwinds]

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
	A	B	C	D	E	F	G	H
1		a	b	c	e
2	1	0	0	0	0
3	2	1	0	3	1		3	c2
4	3	0	1	2	0
5	4	5	0	0	0
6
Sheet3