Compare multiple columns with multiple variables

I'm going crazy looking for this formula. I've tried vlookup, match, and index but cant find the perfect formula to give me what im looking for. So here is what i am looking to accomplish:


Inspector A B C D
Room #
1 T T T F
2 F T F T
3 T F F T



And this is my data
room Inspector
1 A
1 B
1 C
2 B
2 D
3 A
3 D

My mission is to check off which inspector checked each room so all im looking for is a true or false i can convert to a check or X. I have the inspectors names on a side column as well. I understand each inspector needs its own formula, but i need a formula that will go through my data, find the reference room, once that is true it will look for the inspectors that have inspected that room and look up if its true for a specific inspector. It is quite difficult to explain without sending over the worksheet.

If anyone can help it would be huge. I have 15000 items and 1000 rooms and the worksheet needs to be done by today.

Assuming your raw data is in Sheet1, A1:B8

and then your table is in Sheet2, A1 with Room numbers starting in A2 and Inspectors starting in B1.. then in B2:

=LEFT(SUMPRODUCT(--(Sheet1!$A$2:$A$8=$A2),--(Sheet1!$B$2:$B$8=B$1))>0)

copied down and across.

or in XL2007

=LEFT(COUNTIFS(Sheet1!$A$2:$A$8,$A2,Sheet1!$B$2:$B$8,B$1)>0)

adjust ranges to suit...

Can you post a sample so we can better visualize what you mean?

or maybe this might be what you are after?

=LEFT(SUMPRODUCT(--($R$4:$R$13189=$A14),--(ISNUMBER(MATCH($S$4:$S$13189,$L$6:$L$8,0))))>0)

Not sure if it would be much more efficient, but if you kept the headings in F3:H3 consistent with the items in column K, then you could maybe use:

Please Login or Register  to view this content.

in F4 and copied down the column and over