Formula to Retrieve Value base on specific text.

[Kingkoopa]

Hi Guys,

I am using Excel 2016. Its been awhile since I posted would appreciate some help on the following problem.

I have the following list :

Fruit	Price
Apple	5.00
Watermelon	6.00
Mango	4.50

<tbody>
</tbody>

Fruit	Price
Watermelone 5KG
Banana 300G
Red Apple 300G
Mango 2.50KG
Green Apple 200G
Mango 10KG
Watermelone 500G
Pear 5KG

<tbody>
</tbody>

Would like to get the following result :

Fruit	Price
Watermelone 5KG	6.00
Banana 300G	#N/A
Red Apple 300G	5.00
Mango 2.50KG	4.50
Green Apple 200G	5.00
Mango 10KG	4.50
Watermelone 500G	6.00
Pear 5KG	#N/A

<tbody>
</tbody>

Basically if the text is found in the table it would list the corresponding price. Been messing around with a lot of lookup, search, countif combination of formula's. But can't seem to figure out a way to get the price .

 

[Fluff]

How about

Book1
	A	B	C	D	E	F
1	Fruit	Price			Fruit	Price
2	Apple	5			Watermelone 5KG	6
3	Watermelon	6			Banana 300G	0
4	Mango	4.5			Red Apple 300G	5
5					Mango 2.50KG	4.5
6					Green Apple 200G	5
7					Mango 10KG	4.5
8					Watermelone 500G	6
9					Pear 5KG	0
Test

Cell Formulas
Range	Formula
F2	=SUMPRODUCT((ISNUMBER(SEARCH($A$2:$A$4,E2)))*($B$2:$B$4))

 

[bosco_yip]

Or............

In F2, copied down :

=LOOKUP(1,-FIND($A$2:$A$4,E2),$B$2:$B$4)

Regards

 

[Kingkoopa]

Thanks guys it worked :D

 

[Fluff]

Glad we could help & thanks for the feedback