Need to write a search array formula to search different columns

[healthseo]

Hello, I need to write a search formula that will search for text in several columns and then return a value in a separate cell. The value returned will be taken from the cell to the left of where the text is discovered. If I use the following table as an example I want to report the price of apples in each shop so I will have a separate formula for each shop. The answers should be Shop 1 .40, Shop 2 .50, Shop 3 .60. It also worth noting that sometimes the text in the cell I am looking for is different eg green apple, red apple so I need to pick out the work apple. Thanks

	A	B	C	D	E	F	G	H
Shop 1	.40	green apple	1	banana	.2	strawberries	2	pineapple
Shop 2	2	banana	.50	delicious apple
Shop 3	3	melon	.5	strawberries	.6	apple packs
Shop 4	3	mango	3	sweet pineapple


<tbody>
</tbody>

 

[Daveydoodles]

Try =INDEX(A2:H2,MATCH("*"&"apple"&"*",A2:H2,0)-1)

 

[healthseo]

Thanks for this. Works great. The only problem is that I am left with N/As in the blank cells. Can I adjust the formula to leave these blank?

 

[Daveydoodles]

You can use IFERROR(INDEX(A2:H2,MATCH("*"&"apple"&"*",A2:H2,0)-1),0)
or IFERROR(INDEX(A2:H2,MATCH("*"&"apple"&"*",A2:H2,0)-1),"")

depending on whether you want a 0 or blank text when there's an error.

I'm also unsure how this formula deals with lines when it doesn't find apple, like shop 4 in your example, worth looking out for incase it's adding extra values when it doesn't find apple