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 :

[TABLE="width: 200"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Watermelon[/TD]
[TD]6.00[/TD]
[/TR]
[TR]
[TD]Mango[/TD]
[TD]4.50[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 214"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Watermelone 5KG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana 300G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red Apple 300G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mango 2.50KG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green Apple 200G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mango 10KG[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Watermelone 500G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pear 5KG[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Would like to get the following result :

[TABLE="width: 214"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Watermelone 5KG[/TD]
[TD] 6.00[/TD]
[/TR]
[TR]
[TD]Banana 300G[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Red Apple 300G[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]Mango 2.50KG[/TD]
[TD] 4.50[/TD]
[/TR]
[TR]
[TD]Green Apple 200G[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]Mango 10KG[/TD]
[TD] 4.50[/TD]
[/TR]
[TR]
[TD]Watermelone 500G[/TD]
[TD] 6.00[/TD]
[/TR]
[TR]
[TD]Pear 5KG[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


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