How to Perform Lookup on Alphanumeric Data?

[Damian Carrillo]

I've found a few hundred posts about VLOOKUP and HLOOKUP functions and
their limitations. But I've not found a post that addresses my
particular situation.

Is there a way in Excel to get a VLOOKUP to find the next highest value
in a Table where the lookup data is alphanumeric? I have a list of
names in Column A and I'm trying to perform a lookup on a table in
another spreadsheet using:

=VLOOKUP(A20,'Employee List'!$A$2:$G$1769,2)

But the problem is that the lists are from different sources, so the
spacing, punctuation, and inclusion of middle names/initials varies.
The only constant is that both tables are sorted in ascending order and
both are in last-name-first order.

A B C
"BOB, BETTY B" 04941 02
"BOB, BILLY BO" 09498 01
"BOB, SUSIE Q" 01842 01

FALSE fails most of the time because the entries rarely match exactly.
TRUE returns matches for the values that fail with FALSE, but those
values are always the next lowest value, which is never correct.

Is there a way to look for "BOB, BILL" or "BOB BILLY BO" and return the
corresponding text "09498" or "01" from an adjacent column?

 

[Damian Carrillo]

I should probably add that I use Microsoft Office XP Pro and that I
have tried using the INDEX/MATCH functions suggested in other replies
about VLOOKUP, but I cannot seem to make it work.

 

[Bill]

I am not sure I understand your situation. It sounds like you have a lookup
value occupying a single cell but your reference table consists of entries
where the matching data would occupy two different cells. Is that the case?

This is the part I don't understand from your first message:

A B C
"BOB, BETTY B" 04941 02
"BOB, BILLY BO" 09498 01
"BOB, SUSIE Q" 01842 01

Is there anything in column B?

Anyway, it could be that the best advice is for you to bite the bullet and
rebuild your reference table so that the entries are consistent and in a
more predictable in format,and compliment that by entering your lookup value
in the same format.

One other possibility is that you use Data Validation to ensure the name is
entered accurately, but with 1769 entries that may not be very useable.

Really it seems you are talking about a rather sophisticated search engine
that finds things that look approximately like items in a set of reference
data, but not exactly.

 

[Damian Carrillo]

My table lettering was misleading since the format changed after I
posted it. The names are in column A, a 5-character alphanumeric value
is in column B and a 2-digit alphanumeric value is in column C.

"BOB, BETTY B" 04941 02
"BOB, BILLY BO" 09498 01
"BOB, SUSIE Q" 01842 01

I guess I was working from the the view that alphanumeric values could
be searched in the same was as numeric values but that may not be the
case.

Perhaps there's a way to strip out punctuation characters and extra
spaces to try and make the data more uniform as the analysis happens.
I don't have access to modify the reference table, only view and
reference the existing data.

The exception is to make sure all the symbols dividing first and last
name are commas. I have asked for this to be uniform and that change
has been made, though the periods after initials, etc, are still not
consistent.

 

[patrick]

Damian,
Sounds to me like the problem is in the 2nd sheet. Were it me, I would
purify my data in that sheet using Trim>Left>Right>Middle functions to erase
all unecessary spaces and other inconsistent formatting. You could then use

concantenate to shove the names back together with you controlling the

formatting. Then your VLookup should work.
Is a pivot table the answer for you with your current formatting>
Pat

 

[Damian Carrillo]

Patrick,

But as I said the problem is that I can't really modify the 2nd table
and an update is pushed to it daily from another system in another
department. Though after reading your post, I realize there may be one
option availible, which is to make a macro that copies the second
worksheet and modifies the copy to my parameters. I could schedule it
to run on open so that everytime I opened the document it would give a
prompt asking if I wanted to update the data in my copied sheet.

I'm not sure about a pivot table... I've not really used them. I'm not
quite sure what they do or how they work but I can investigate that
route too? Thanks for the advice!