Confirming job designators and titles over multiple cells

[octagonaway]

Greetings Everyone,

I've recently gotten a job where I have to use excel extensively. I have excel experience but haven't used it powerfully in years. I'm using Excel 2011 on a Mac.

I'm currently having trouble on what formula to use. I'm trying to determine between active and outdated job designators. The first sheet has the job designator code and job titles in separate columns. The second sheet has the job designator and job titles in separate columns along with a column that says which set of job titles and job designators is active and outdated. I have thousands of these to go through.


Does anyone know a formula that I can use that will show me which sets of job titles and designator are active or outdated based on sheet 2?

I have included a sample excel of what I'm trying to do.

Thank you in advance.

[Pete_UK]

This should do it - put this formula in C2 of Sheet1:

=IF(ISNA(MATCH(B2,Sheet2!B:B,0)),"",IF(LEFT(VLOOKUP(B2,Sheet2!B:C,2,0))="A","Active","Obsolete"))

and copy down to cover your data.

Hope this helps.

Pete

[octagonaway]

Pete, Thank you for the quick reply on this. It works great for the sample that I gave but not for the actual file I have. Although, that is entirely my fault as I didn't put into the sample excel that both the job designator and job titles have multiple values.

I have updated the sample excel for that. Again, I'm sorry for my miscommunication and hope that someone can help me.

[Pete_UK]

The second file isn't very different from the first, and the same formula seems to work just as well with it.

Pete

[octagonaway]

Pete, sorry about that. I had to readujust the formula a little for the actual file. Sorry about that.

Thank you very much for the help.