VLOOKUP and LEN/ISNA to match names?

[LTUser54]

I have a worksheet with name info from 2 databases. I'm trying to match
names in different columns that have different formats. One has LN, FN,
Initial format, presented in 1 column (Column B), the other has LN,FN
with info in 2 columns (Cols. H:I), all names sorted alpha ascending.


Due to the queries involved (PeopleSoft & Employease DB's), I do not
have a matching determinate like SSN.

I poked around using MS Help - that was an exercise in frustration, and
even tried to cobble together a funtion using VLOOKUP and ISNA was
futile because of the name length mismatch. The shortest employee name
is 4 characters.

LEN only returns the actual length of the text string, and I can't
figure out how to solve this. Can you help with this problem? Any help
is sincerely appreciated.

Mark
Boston

 

[aidan.heritage]

Sounds familiar - I would build a NEW column in each workbook that
contains the combined data you want to match

eg

=LN & " " & FN

You can then lookup on this column to get the data you need (exactly
what I've just had to do for other data from peoplesoft for my father
in law, who is high up in HR for a large UK company!!)

 

[LTUser54]

I can easily do that, but the problem is there is no match with the FN
and/or initial from the concatenated name that will match the B column,
which has LN,FN Initial format.

Do you see the dilemma?

In addition, I would like to get a FALSE notation in the formula cell,
so it prints "OK" or "LN Mismatch" or something like that, if there is
no LN name match between the two columns.

If somebody can tell me a LN VLOOKUP match between the 2 columns I will
be very grateful...

Mark
boston

 

[aidan.heritage]

My THOUGHT was to build on BOTH sheets a column that contained values
you could then match - but I can see that if the data is formatted in
such a way that you CANNOT do it you are going to have problems -
possibly counting the instance of the surname and matching off the
unique values, then working through the rest would be a possible
solution - especially if this is a one off or occassional event!

 

[aidan.heritage]

In terms of the error trap, that's easy - if a little long

=if(iserror(vlookup(etc),"error message",vlookup(etc))

it's long because you enter the vlookup formula twice!

 

[LTUser54]

Not to be obtruse and dense and stuff, but... since I'm comparing a LN
to a LN, (note comma)FN MI format, wont ALL of them have an error
message? hmmm? BTW, the concatenated list has lots of mismatches
because differences in FN's and MI's, IE: Murphy, John vs. Murphy,
Johnny J. and that kind of thing.

So, if I can use the IF(iserror(vlookup formula, how do I really write
this, please? Not trying to be a pain, but with these formulas, one
wrong paranthsis or colon and it's a failure, and I'm not that skilled
with more complicated formulas.

Isn't here a way to compare the first few characters of the LN column
to the first few charaters of the LN,FN MI column? maybe using LEN or
ISNA?? Since this is a short list, there are no duplicates in the
first 4 characters of each LN.

It would be ideal to compare the "textstring and-then-space" cell to
the "teststring up to comma" cell with a formula, that would give the
cleanest dataset. Can anyone suggest a formula that will do this? Too
bad Excel is so crappy at this kind of thing (and PLEASE, nobody
suggest a macro! those are IMPOSSIBLE for me!)

any help is truly appreciated


Mark in Boston

 

[Ken Hudson]

Seems to me that you want a formula that extracts the last name from column B.
Insert a new column C.
The foillowing formula in C1 will extract the last name from B1. The formula
assumes that a comma defines the end of the last name.

=LEFT(B1,FIND(",",B1,1)-1)

Copy this formula down column C.

Then enter your VLOOKUP formula using column C data.