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?