Vlookup issue with asterik in value

[MikeGeno]

I am wondering if there is a workaround for a vlookup issue I am having.

I have a list of item codes, many of them contain an asterik.

Here is an example:

=vlookup(a2,d2:e3,2,)

Item...................FAMILY..........Sort 1 result...........Sort 2 result
1SV*S23BLS SVS 2,000 2,000
1SV*23BLS SVTC 2,000 5

Sort 1 order in dataset

Item Units
1SV*S23BLS 2,000
1SV*23BLS 5

Sort 2 order in dataset

Item Units
1SV*23BLS 2,000
1SV*S23BLS 5

Depending on the sort order of my data tab I get a different result for the vlookup (Knowing that vlookup finds the first matching value in a dataset and the asterik acts a a wild card)

In this instance I can sort product family descending and I get the correct result. In other cases the product family names may need to be sorted ascending to stop the odd result from happening. To keep resorting pieces of the data tab or write a new vlookup for each specific case takes time and is not a practical solution


Does anyone know of a good workaround for this. I hope my explanation is clear.

 

[Andrew Poulsom]

For an exact match you need:

=VLOOKUP(A2,D2:E3,2,FALSE)

Without it Excel assumes that your data is sorted ascending.

 

[MikeGeno]

I've always left off the false, as long as you have the comma after the column reference it assumes false, has always worked for me reading it as false.

I just added it to my formula try and I still get the same results as shown above. it seems to ignore the S following the asterik and gives me a bad value depending on how they are sorted.

 

[Andrew Poulsom]

The asterisk in the lookup value id being treated as a wildcard. Try:

=VLOOKUP(SUBSTITUTE(A2,"*","~*"),D$2:E$3,2,FALSE)

The default for the fourth argument is TRUE.

 

[MikeGeno]

That works perfectly. Thank you so much for the help! This will save me many hours of going back and retracing my steps to make sure data ties out.