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 which was futile because of the name length mismatch. The shortest employee name is 4 characters. This is a short list to match, but tying to automate it is driving me nuts.

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

[[email protected]]

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

[[email protected]]

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!

[[email protected]]

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 obtuse 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 "textstring-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 don't 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.
--
Ken Hudson


"LTUser54" wrote:

>
> 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
>
>
> --
> LTUser54
> ------------------------------------------------------------------------
> LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459
> View this thread: http://www.excelforum.com/showthread...hreadid=544339
>
>