VLook-Up to match names in a different sheet

[brownt]

Hello All,
I'm wondering if it is possible to match the names in two separate spread sheets, in the same file. This is what I'm trying to accomplish:

I have two sheets in the same workbook, they both have names (last, first, middle). However, one sheet shows active names only, and the other shows both active an inactive.

I'm trying to compare the two and isolate only the inactive names. I know VLook-up will probably work, but I'm not able to get the information I need, so I'm know I'm probably doing something wrong. So Is this possible, or is there another way to accomplish this task?

Thanks for any assistance in advance, and have a wonderful day!

 

[jasonb75]

Are the last, first, and middle names all in 1 column, or spread over 3 columns? Which columns?

Which version of excel are you using?

 

[brownt]

Hi Jason,
There are three (3) columns in both spreadsheets.

Thanks!

 

[brownt]

Sorry, I'm using Excel 2007....

 

[jasonb75]

Assuming active names sheet is called Active Last name, First Name, Middle name in columns A B & C, with headers in row 1 and first persons name in row 2, in D2 (or next avaliable column) of the mixed sheet.

=if(countifs(Active!A:A,A2,Active!B:B,B2,Active!C:C,C2),"Active","Inactive")

Then fill down to show the status of each person.

 

[brownt]

Hi Jason,
when I put the formula in the Inactive sheet, everyone shows inactive. Is there something else I'm perhaps not doing correctly?

Thanks for your assistance!

 

[brownt]

Hi Jason,
Thanks for the information. I know this should work looking at the logic. However, for some strange reason, it does not want to work. Is there anything that I'm missing?

Thanks and have a wonderful day!

 

[Blunder1]

maybe in column D on both sheets add formula =A1&A2&A3, then in column E add formula (assuming Sheet 1 is the active names and Sheet2 contains both:-

=IF(ISNA(VLOOKUP(D1,Sheet1!D:D,1,0))=FALSE,"Acitve","Inactive")

 

[jasonb75]

Something I overlooked with the countifs method, all 3 fields need to be populated, if there was no middle name, it would fail to match.

The method that Blunder1 suggested will (or should) work.

 

[brownt]

Blunder1 and Jason,
Thanks so much for all of your assistance. It Worked!!!!

I had one problem...when I imported the names in, there was a space in the first column, which put a space between the last and first name, but not in the middle.

When I typed the names in, the formula worked perfectly. So I had to figure out how to take the space out. This is what I did:

I highlighted the first column with the space (the last name), on the Data tab, I selected 'Text-to-Column', left it selected on delimited, then clicked next, took the check out of 'Tab' and selected 'Space', next, left 'General' selected, and 'Finished'.

The formula worked like a charm !

Thank you so very, very much. I have about 10,000 names to go thru and you saved my week.

Thanks again and have a Wonderful Day!!!!