Look up in a table, and return the value of the first row.

[NNO123]

Hi guys,
I dont think this one is difficult, but I cannot find a good solution
Maybe one of you can help

I have a big table:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]String[/TD]
[TD]String[/TD]
[TD]STring[/TD]
[TD]String[/TD]
[TD]String[/TD]
[/TR]
[TR]
[TD]String[/TD]
[TD]STring[/TD]
[TD]STring[/TD]
[TD]String[/TD]
[TD]STring[/TD]
[/TR]
[TR]
[TD]String[/TD]
[TD]String[/TD]
[TD]String[/TD]
[TD]String[/TD]
[TD]String[/TD]
[/TR]
[TR]
[TD]String[/TD]
[TD]STring[/TD]
[TD]String[/TD]
[TD]String[/TD]
[TD]STring[/TD]
[/TR]
</tbody>[/TABLE]


So in the table I have texts. I want a function, where I can search for a name, and then the function must return the value of the first row.
So Fx, if I find a match in A4, I want it to return the value of cell A1.

Any ideas?

 

[wideboydixon]

Searching across a range is hard and needs an array formula and an INDIRECT() for the only way I know:

Book1
	A	B	C	D	E	F	G	H
1	Apples	Grapes	Oranges	Bananas	Cherries		Look for:	OCIF
2	GYGU	MQFX	VQPF	IRNJ	UPAA		Header:	Bananas
3	YDBZ	XHBX	UKTP	SDHY	WPBD
4	SFML	MXUG	DRMA	MFHA	TZHE
5	NUKQ	NLTX	XDLC	OCIF	LSIL
6	YFIV	GVIS	HVTS	QIJZ	VYFM
7	DMGP	LEIA	SHRR	VWKB	UTTY
8	RTDJ	WFQQ	AYJG	AWQT	DHQC
Sheet1

Cell Formulas
Range	Formula
H2	{=INDIRECT("R1C"&MIN(IF(ISNUMBER(SEARCH($H$1,$A$2:$E$8)),COLUMN($A$2:$E$8))),FALSE)}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[NNO123]

Hi WBD
You are the best! Thank you so much! Very kind of you