Multiformat Lookup

[Arouet]

I'm trying to take a office personnel file and determine if the employees are still active, retired, or unlisted in the building personnel file.. I've tried my best using vlookups, but can't solve it because the names change formats. The names in the office list are in Row A and I want to add a 'Status' Column in for H.

File Formats:

Office List
[Column A]:
[Last, First]
"Smith, John"

Active List
[Column A]:
[Last, First M.I.]
"Smith, John J"

Retired List
[Column A] [Column B]:
[Last] [First M.I.]
["Smith"] ["John J"]

If the names were exact, I believe this would work.
=IF(ISERROR(VLOOKUP(A1,[activelist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
IF(ISERROR(VLOOKUP(A1,[retiredlist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
"Not Found", "Retired"), "Active")

I don't know if there are ways to alter the formula, but I figured I'd have to use macro's. Any help? I figure it should look something like this: I've dabbled in C++ but never VBA

[VBA]
for each name in office

found = "false"
status = "not found"

for each name in active (while found = false)

if names are equal

found = true
status = "active"

for each name in retired (while found = false)

if names are equal

found = true
status = "retired"

[/VBA]
Thank you very much.

[stanleydgrom]

Arouet,

Can you post/attach a workbook with sample worksheets for "Office List", "Active List", and "Retired List ".


Have a great day,
Stan

[Aussiebear]

Hi Arouet Welcome to the forum. When posting sections of code, instead of using [Begin] & [End],please consider useing the vba tag to enclose your code.


You will have some trouble attaching a workbook until you have at least 5 posts to the forum. When you have reached this rating, please click "Go Advanced", to show the additional Options section, then click "Manage Attachments".

[stanleydgrom]

Arouet,

See my Private Message to you (top right hand corner of VBA Express, Welcome, Arouet., "Private Messages:".

Have a great day,
Stan