vlookup question

[Guest]

I have one spreadsheet with a column of products and another spreadsheed with
the same products and prices.
I need to make links from the first sheet to the second one with the prices
to reflect them in the first sheet.
The list of products is sorted differently. This is why I'm going to use
VLOOKUP.

But, the problem is that in the first spreadsheet some products marked with
*, which make them slightly different from those, which should be find in
the second spreadsheet. As an example, BBB and *BBB.
How VLOOKUP could avoid this * to find the same product but without this sign.

Thanks

 

[JulieD]

Hi Alex

if the * occurs in the spreadsheet with the products only then try
=VLOOKUP(IF(LEFT(A1,1)="*",RIGHT(A1,LEN(A1)-1),A1),Sheet2!A2:B11,2,0)

Cheers
JulieD

 

[Frank Kabel]

Hi
you may try the following array formula (entered with
cTRL+SHIFT+eNTER):
INDEX('sheet1'!B1:B100,MATCH("BBB",SUBSTITUTE('sheet1'!A1:A100,"*",""),
0))