VLOOKUP mis-interpretes lookup value

[Kevin Carney]

I have a spreadsheet that uses VLOOKUP to to pull data from price sheets. I punch in the part number and it returns the product name and pricing into the columns next to it. I've simplified the spreadsheet for this example to make things easier.

The problem is when I enter value CLLEDRGB*R, it pulls the information from CLLEDRGB*CR. The two values are not next to each other in the TABLE ARRAY and I have the RANGE set to False so it can only find exact matches.

TS Pricing Worksheet Updated June 12th, 2015.xlsx

Column A is the where I punch in the part number and Column B is where it returns the name. When I enter "CLLEDRGB*R" in Column A, Column B should return "12V RGB Tape Light 12" Segment 20' Reel, 20 PCS TLT12V1RGB12R". But it returns information as if I entered "CLLEDRGB*CR".

Any ideas?

Thanks,

Kevin

[Ron Coderre]

The issue you're having is caused by the wildcards in your search term. In Excel, an asterisk (*) indicates a match of zero or more of any characters/numbers.
You'll need to indicate to Excel that you want the wildcard evaluated literally by putting a tilde (~) in front of it.

Using your posted workbook, try this formula, copied down:

Please Login or Register  to view this content.

Is that something you can work with?

[Kevin Carney]

PERFECT! Thank you so much!