Looking for formula to find data in array

[hammerhead13]

I am trying to get a Formula that will Give me the Name in "B1" when I enter an Item in "A1". Any help is greatly appreciated?

	A	B	C	D
1	Bananas	Mike
2
3
4	Bob	Apples	Tomatoes	Figs
5	Ted	Oranges	Watermelon	Cucumbers
6	Bill	Grapes	Carrots	Potatoes
7	Mike	Pears	Melon	Bananas

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As long as you have no duplicate values maybe

+Fluff 1.xlsm
	A	B	C	D
1	Bananas	Mike
2
3
4	Bob	Apples	Tomatoes	Figs
5	Ted	Oranges	Watermelon	Cucumbers
6	Bill	Grapes	Carrots	Potatoes
7	Mike	Pears	Melon	Bananas
Test

Cell Formulas
Range	Formula
B1	B1	=INDEX(A:A,SUMPRODUCT((B4:D7=A1)*ROW(A4:A7)))

 

[hammerhead13]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As long as you have no duplicate values maybe

+Fluff 1.xlsm
	A	B	C	D
1	Bananas	Mike
2
3
4	Bob	Apples	Tomatoes	Figs
5	Ted	Oranges	Watermelon	Cucumbers
6	Bill	Grapes	Carrots	Potatoes
7	Mike	Pears	Melon	Bananas
Test

Cell Formulas
Range	Formula
B1	B1	=INDEX(A:A,SUMPRODUCT((B4:D7=A1)*ROW(A4:A7)))

Thank you very much for the reply. When I use the formula it returns "True".

 

[Fluff]

It would only return true if you changed the formula, or you have the word True in col A