I need to find a specfic text in a range of cells and return specfic text

[ramjdmcmm]

I need to find specific text in a range of cells and return the text two columns to the right of the cell that contains that text.
If any of the cells A1:E6 contain an "a" I need to return the text in column h on the same line that the "a" is in.

Capture.JPG

Thanks in advance,
Richard

[Tony Valko]

You edited your post so my suggestion no longer applies!

[ChemistB]

Assuming there's only going to be one cell with "a" in it

=INDEX($H$2:$H$6,SUMPRODUCT(($A$2:$E$6="a")*(ROW($A$2:$A$6)-1)))

[ramjdmcmm]

I tried Vlookup. Didn't work.

[ramjdmcmm]

I failed to mention I need to do the same for the all the text a,b,c,d,e,f all so.

[ChemistB]

Just change ="a" to whatever you are looking for, OR reference a cell (i.e. $A$2:$E$6=A10) and just put what you are looking for in A10

[Tony Valko]

Here's another one...

Data Range

	A	B	C	D	E	F	G	H
1	V1	V2	V3	V4	V5
2	A							Test1
3			B					Test2
4					D			Test3
5				E				Test4
6		C						Test5
7
8
9
10	A	Test1
11	B	Test2
12	C	Test5
13	D	Test3
14	E	Test4
15	-----	-----	-----	-----	-----	-----	-----	-----

This formula entered in B10 and copied down:

=INDEX(H$2:H$6,MATCH(1,MMULT(--(A$2:E$6=A10),{1;1;1;1;1}),0))