Vlookup based on multi search

[chandrashekar]

Hello Team,

Could you please help on vlookup formula based on multi search
[TABLE="width: 230"]
<tbody>[TR]
[TD]Desc[/TD]
[TD]Formula Output[/TD]
[/TR]
[TR]
[TD="align: center"]this is chair[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]apple on the tree[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]Chair broken[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]Fresh Tomoato[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]
[TABLE="width: 168"]
<tbody>[TR]
[TD]Vlookup Search[/TD]
[TD]Desc Code[/TD]
[/TR]
[TR]
[TD]Chair[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Tomoto[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

Regards,

Chandru​

[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

 

[Chrisdontm]

For your search criteria add in a wild card on each side of the look up value.

For example;

=VLOOKUP("*"&"chair"&"*",$A$1:$B$3,2,0)


If your lookup value is in another cell then use that cell inside the formula;


=VLOOKUP("*"&$H$3&"*",$A$1:$B$3,2,0)

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]Desc[/td][td]Formula Output[/td][/tr]
[tr][td]

2​

[/td][td]this is chair[/td][td]

1​

[/td][/tr]
[tr][td]

3​

[/td][td]apple on the tree[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]Chair broken[/td][td]

1​

[/td][/tr]
[tr][td]

5​

[/td][td]Fresh Tomato[/td][td]

3​

[/td][/tr]
[tr][td]

6​

[/td][td]Vlookup Search[/td][td]Desc Code[/td][/tr]
[tr][td]

7​

[/td][td]Chair[/td][td]

1​

[/td][/tr]
[tr][td]

8​

[/td][td]Apple[/td][td]

2​

[/td][/tr]
[tr][td]

9​

[/td][td]Tomato[/td][td]

3​

[/td][/tr]
[/table]

In B2 enter and copy down (to B5)...

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$7:$A$9&" "," "&$A2&" "),$B$7:$B$9)

 

[chandrashekar]

Hello Aladin,

Thanks its working fine


Regards,

Chandru

 

[Aladin Akyurek]

Hello Aladin,

Thanks its working fine


Regards,

Chandru

You are welcome.

 

[chandrashekar]

Hello Aladin,

Can you please explain how this formula works (=LOOKUP(9.99999999999999E+307). I tried using evaluate formula but did not understand it

Regards,

Chandrashekar B

 

[Aladin Akyurek]

Hello Aladin,

Can you please explain how this formula works (=LOOKUP(9.99999999999999E+307). I tried using evaluate formula but did not understand it

Regards,

Chandrashekar B

See if this helps:
https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323

 

[chandrashekar]

Hello Aladin,

Thanks it helped me a lot

Regards,

Chandru

 

[Aladin Akyurek]

Hello Aladin,

Thanks it helped me a lot

Regards,

Chandru

Glad to hear that. Thanks much for the update.