Match Specific Text and lookup value for that

[Bipin619]

Dear All,

I have two set of different product name. One is with price column and other is without it.


Set 1
e.g MD11111HN/A Apple iphone 5s 18000
MD11515HN/A Apple iphone 6s 28000


Set 2 Price
Apple iphone 5s ?
Apple iphone 6s ?

Now i want to lookup price for Product in Set 2 from set 1 by matching specific text contained in set 1. I mean which is common in both set 1 and set 2.

Which formula should i use ? Thanks in advance.

 

[bosco_yip]

In B2, formula copy down :

=LOOKUP(9^9,SEARCH(A2,D$2:D$3),E$2:E$3)

Regards
Bosco

 

[Bipin619]

Dear Bosco,

Thank you so much. it works.

 

[AVK]

In another way..
Suppose your data in in "A" is Description & "B" is Rate.
In "D2" criteria mentioned *Apple iphone 5s* & "D3" *Apple iphone 6s*
(Mark * before & after in criteria)
In "E2" =IFERROR(INDEX($B$2:$B$3,MATCH($D2,$A$2:$A$3,0)),"")
Array formula hence shift+ctrl+enter

 

[rahulshewale1]

Suppose your data in in "A" is Description & "B" is Rate.
In "F2" criteria mentioned "Apple iphone 5s"


=VLOOKUP("*"&F2,$A$1:$B$3,2,FALSE)


thanks
rahul shewale