Matching cells across sheets and returning color value based on the sheet

I have a set of sheets on a workbook that contain addresses, which are all combined on the first sheet (as you can see in the attached file). I would like to use a formula to compare the addresses on the Main sheet with the other sheets, then return in the D column of Main a colored cell indicating on which sheet the address was found on; different colors for different sheets. I tried to figure out some conditional formatting, but to no avail.
Also, on occasion I have spreadsheets with 5 or 6 sheets containing these mixed addresses. Is there a solution that won't be limited to just a few sheets for comparison?

Excel Help.xlsx


Thanks

Instead of returning a colored cell why not just return the sheet name?

How about returning the sheet name to cells that are out of sight in an unused area of the sheet? Say column AA? Then, you can use conditional formatting in column D based on the sheet name in column AA.

Try this...

Create this defined named formula...

Goto the Formulas tab>Define name
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
OK out

Then, with your addresses on the Main sheet in the range A2:A9...

Enter this formula in say AA2 and copy down to AA9:

=LOOKUP(2,1/COUNTIF(INDIRECT("'"&SheetNames&"'!A2:A10"),A2),INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),0))

Then, you can use conditional formatting in column D based on the sheet name in column AA.

I applied this in your sample file as:

Sheet1 = a shade of green
Sheet2 = a shade of red (maroon?)
Sheet3 = a shade of blue

You'll have to save the file as a macro enabled file in the *.xlsm file format.

Here's your file with this implemented:

Excel Help (1).xlsm

Improvement!

We can shorten the sheet name formula a few keystrokes:

=LOOKUP(2,1/COUNTIF(INDIRECT("'"&SheetNames&"'!A2:A10"),A2),MID(SheetNames,FIND("]",SheetNames)+1,31))

You're welcome. Thanks for the feedback!

If your question has been answered please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools>Mark this thread as solved.