Vlookup in ~ or special character

[HarunSumon]

Dear Concern,

I need to lookup value even though having special character in between. e.g.

in a table one column written as "POWER 0132: Hardware Installation and Supervision, Inverter 2~3 Cabinet (Outside Dhaka)" and next right column is "SITE".

my Query is, how to use vlookup in this case to lookup value "SITE" in another workbook?

Please help with solution at your earliest.

Thanks,
HarunSumon

 

[shyy]

use two ~~

Excel 2010

	A	B
POWER 0132: Hardware Installation and Supervision, Inverter 2~3 Cabinet (Outside Dhaka)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]SITE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]SITE[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=VLOOKUP("*"&"~~"&"*",$A$1:$B$1,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

If you add a SUBSTITUTE function into the lookup value of a regular VLOOKUP, to replace "~" with "~~" as shyy says, that that will work whether the lookup value contains "~" or not, e.g.

=VLOOKUP(SUBSTITUTE(A2,"~","~~"),B$2:C$100,2,0)

 

[HarunSumon]

use two ~~

Excel 2010

	A	B
POWER 0132: Hardware Installation and Supervision, Inverter 2~3 Cabinet (Outside Dhaka)

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]SITE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]SITE[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=VLOOKUP("*"&"~~"&"*",$A$1:$B$1,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[Thanks for your prompt reply ]

 

[HarunSumon]

If you add a SUBSTITUTE function into the lookup value of a regular VLOOKUP, to replace "~" with "~~" as shyy says, that that will work whether the lookup value contains "~" or not, e.g.

=VLOOKUP(SUBSTITUTE(A2,"~","~~"),B$2:C$100,2,0)

your reply was outstanding and I applied it in my problem for solution. Thank you once again