Identifying a match on a value on multiple worksheets

[janetk411]

Good afternoon,
I have tried to use an IF statement using MATCH on this but it has not been working for me.
I have three worksheets containing invoice numbers
One is a Summary
The other two represent the East and West Region.
Each of the region worksheets contain invoice numbers from their respective region.

Then on the summary worksheet I want to be able to identify which region has those invoices as shown in Column B.
Column B on the Summary would show EAST, WEST, BOTH or NONE...

Can someone possibly help me with this formula.
I have attached an example of the spreadsheets I am working with.

Thank you !

Janet

 

[Legacy 143009]

Assuming your values are on column A and sheet names are WEST REGION and EAST REGION. Paste this to SUMMARY B1:

=IF(COUNTIF('WEST REGION'!A:A,A1)*COUNTIF('EAST REGION'!A:A,A1)>0,"BOTH",IF(AND(COUNTIF('WEST REGION'!A:A,A1)>0,COUNTIF('EAST REGION'!A:A,A1)=0),"WEST",IF(AND(COUNTIF('WEST REGION'!A:A,A1)=0,COUNTIF('EAST REGION'!A:A,A1)>0),"EAST","NONE")))

 

[janetk411]

Thank you very much! That worked!

 

[Legacy 143009]

Glad it did work