Problem with nested if formula in comparing 2 worksheets

[budifb]

Hi there,
This is my first time to post an excel question online so I hope I am able to explain my issue clearly.

I have 2 employee roster spreadsheets, one from previous month and another from current month. I created a 3rd worksheet to track changes between them. For a particular column I want to see which employees had a change in status so I wrote this formula -

=IF((VLOOKUP($A2,Previous,12,FALSE))=(VLOOKUP($A2,Current,12,FALSE))," ",VLOOKUP($A2,Previous,12,FALSE))

A2 is the employee number, Column "12" is the status column, "Previous" is the named range in the previous month's roster, and "Current" is the named range in the current month's roster. I would like the cell to show the previous employee status if there is a change and null if no change. The formula works except when it doesn't find a match (employees that don't exist in both worksheets), it gives an #N/A. How do I get rid of the #N/A? I tried to incorporate "ISNA's" within the formula but I wasn't sure where to place them.

Also, please let me know if there is a simpler way to do this. I am comparing a whole bunch of columns and lots of vlookups is making my file very large. THANK YOU SOOOOOOOOO MUCH for your help in advance

 

[mikerickson]

I would try something like

=IF(ISNA(VLOOKUP($A2,Previous,12,False)), "new hire", [your formula])

 

[budifb]

thank you very much!!! i appreciate the quick response.