Search and return

[KasperMikkelsen]

Hi,

I would like to know how to search for a cell whose value matches that of another cell and get the cell that is "x" cells above or below that cell. I have uploaded an Excel-document with animals as the example. Could anyone help me do this?

Kind regards
Kasper

[mikeTRON]

In Cell B2:

Please Login or Register  to view this content.

In cell B4:

Please Login or Register  to view this content.

[Phuocam]

Try this in B2:

=INDEX(Animal!$A$2:$A$16,MATCH(B1,Animal!$A$1:$A$15,0))

Copy down.

[KasperMikkelsen]

Thanks a lot!

[mikeTRON]

Try this in B2:

=INDEX(Animal!$A$2:$A$16,MATCH(B1,Animal!$A$1:$A$15,0))

Copy down.

The formula only works for B2, not B4, as the match brings back the same number no matter what row the formula is in.

[AlKey]

Enter formula in B2 and copy down

Please Login or Register  to view this content.

v	A	B
1	Animal:	Cow
2	Animal in the cell below that:	Fish
3
4	Animal three cells below that:	Snake