Advanced Match Help (possibly Index Match)

Straight into it:
In sheet 1 I have all data.
I have it sorted so that each unique entry in column A gets its own sheet with A1 of the new sheet being the entry in column A of sheet 1; and column b in the new sheet being all unique entries of column b of sheet 1..
In each sheet that gets created (sheet 2, sheet 3, etc) I want to add a formula in column c and d, that says, (if on sheet 1, the values of column A match that of sheet 2, and the values of column b match that of sheet 2, and there is an "X" in column C of sheet 1, then mark an x in column C of sheet 2)... (column D of each new sheet would be the same except it would search column D of sheet 1).

This idea is simple, however the problem is that when there are duplicates in column B of sheet 1, an Index, Match function would spit back the column c & d of the top entry of the duplicate instead of each one being true...

See my attached file.. The proper output on sheet 2 would be and X in column C & D next to Jake.

Any help is very helpful

Thanks

It's a little ugly, but doable:

C1

Formula:

Please Login or Register  to view this content.

D1

Formula:

Please Login or Register  to view this content.

These are arrays, entered with Ctrl+Shift+Enter instead of hitting Enter as normal after you copy in the formula.


From there you can copy C1 & D1 downwards, and then copy C1:D6 over to tab 3 in the same spot.

EHF example.xlsx

Try:

Cell B1:

Formula:

Please Login or Register  to view this content.

and copy down and across.

Regards, TMS

You're welcome, ... I think