VLOOKUP Code For Using Multiple Variations Of Original Number

[FredGwin]

I have an attendance sheet that we will be using a barcode scanner to enter their invitation as they arrive.
The data I was given works fine with the following


=IF(B2="","",VLOOKUP(B2,'NAME LIST'!$A$2:$B$3000,2,FALSE))

With
[TABLE="width: 176"]
<tbody>[TR]
[TD="width: 235, bgcolor: transparent"]*00361428*[/TD]
[/TR]
</tbody>[/TABLE]
As my barcode format


the problem is when another team member used mail merge to send the invitations to the print company, we now have a check digit at the end of the barcode when scanned, therefore I get #N/A


I have changed the TRUE FALSE but that produces too many similar results.
What formula can I use to have the letters, ABCDEF etc.. added to the lookup?

 

[brucef2112]

A bit more detail about the ABC's part please.
Where are the letters (in a separate column, part of the name?) and which ones do you want to add?
Are your barcode really delimited with *'s or is this just emphasizing?
I'll assume this formula is on a different worksheet. but please clarify.

The one thing I can answer for sure is: You want to leave the last Argument of the Vlookup as FALSE!
This is related to if your data is sorted ascending or just random for your search column.
FALSE tells Mr VLookup, I don't care if its alot of work, I want you to look through the entire column B until you find a match. Only if you get to the very end without a match, will you tell me #N/A.
TRUE tells Mr VLookup, Ok To help you out, I've heard the data is in ascending order. I want you to look through column B starting at the top down. but as you work your way down, if you find yourself past the accending point where
the data should have been, just go ahead and quit early and tell me #N/A.
so,
if Mr Vlookup is given a FALSE Token and is told to find "Garry". Mr V will look in column B for Garry and must not stop until he finds him, even if it means going all the way to the last cell row of data. At that point he can bring back #N/A if not found.
if Mr Vlookup is given a TRUE Token and is told to find "Garry". Mr V will look in column B, BUT because he has the rumor it's been sorted (true token), if he sees he has gone past the "G"'s then he'll stop the search and bring back a #N/A and call it a day. if the data is sorted he gets out of doinig extra work. but if it isn't sorted and given the true token he is just going to assume it was and quits when he can. So if the first row is "Adam", second row has "Mark" then Mr V will stop the search at row two.

I've been Excel'ing for 15 years and have NEVER given a TRUE token to Mr V. Yehhhh, I'm a hard @ss that way. but better he works hard than me.

Anyways I'll take some crazy blind wack to help without more details. If its extra stuff added to the source data, what about using LEFT(,,) MID(,,,) RIGHT(,,) functions to remove the offending characters.
Example; to remove the * from the invitation: If all numbers are 8 characters then an easy fix is a formula like =MID(B2, 2,8)
If these letters you speak of are on the front or end of the number you can use LEFT() or Right() If in the middle somewhere a combo of all 3 could be used or a couple of Mid()s together.

Hope it helps,
b