Create a Match formula to look for a result within two sheets...

[tibbetsj]

I am working on automating a process. Part of the process is to detect when new people appear on the raw data. I am trying to come up with a formula that will look for the persons ID3 on 2 sheets and tell me if they do not exist.

I came up with:
=IF(ISNA(MATCH(A4,NH!A:A,0)),MATCH(A4,BH!A:A,0),"New")

But I can already see the flaw that it will not work properly. I am stuck in trying to figure something out. The only way I can think of to do it would take three cells to do it. Each with a different function to search one sheet. Then a comparison. However I would like to see if I can get it down to just one function in one cell.

Any advice is appreciated.

Thank you,
James

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...
=IF(AND(ISNA(MATCH(A4,NH!A:A,0)),ISNA(MATCH(A4,BH!A:A,0))),"New","Found")

Hope this helps

M.

 

[tibbetsj]

Perfect. I was trying or with no luck. This works perfectly.

Thank you,
James

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.