Remove Wildcard in lookup (2003)

Topic

New Reply

We have part numbers in our system that end with an “*”. At the same time I have near matching part numbers that do not have the “*” I am looking to pull data via Vlookup, but the lookup on the “*” part number pulls the data from the non-asterisk p/n. I need a formula that will recognize each individual part number without the wildcard issue.

I have attached a file to help make more sense.

As always any help is GREATLY appreciated.

JG

Reply | Quote

Replies

You could enter a ~ before the asterisk in A3 to tell Excel to interpret it as a literal character.

Or you could use the following array formula in B2 (confirm with Ctrl+Shift+Enter), and fill down:

=INDEX($E$2:$E$13,MAX(ROW($1:$12)*($D$2:$D$13=A2)))

Reply | Quote

Thank you for all the help. It worked perfectly.

Cheers

Reply | Quote

JG

Utilise the substite function like so and it will do the job

=VLOOKUP(SUBSTITUTE(A2,”*”,”~”),$D$2:$E$13,2)

Reply | Quote