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[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Shop 1[/TD]
[TD].40[/TD]
[TD]green apple[/TD]
[TD]1[/TD]
[TD]banana[/TD]
[TD].2[/TD]
[TD]strawberries[/TD]
[TD]2[/TD]
[TD]pineapple[/TD]
[/TR]
[TR]
[TD]Shop 2[/TD]
[TD]2[/TD]
[TD]banana[/TD]
[TD].50[/TD]
[TD]delicious apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shop 3 [/TD]
[TD]3[/TD]
[TD]melon[/TD]
[TD].5[/TD]
[TD]strawberries[/TD]
[TD].6[/TD]
[TD]apple packs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shop 4[/TD]
[TD]3[/TD]
[TD]mango[/TD]
[TD]3[/TD]
[TD]sweet pineapple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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