Comparing Columns 1 & 2 vs. Columns 3 & 4

[bondra76]

Got two sets of data that I want to compare against each other. Here's what the data sets look like -

Contract #	Supplier	Approved?	Service	LookupKey
1	ABC Company	Yes	Trash Removal	1;Trash Removal
1	ABC Company	Yes	Landscaping	1; Landscaping
2	Bobs Company	No	Landscaping
3	XYZ Company	Yes	Arborist	3; Arborist

<tbody>
</tbody>

I created a lookupkey that's a simple if statement to check if the contract is approved; if it is then Contract # and service are combined and a lookup key is populated. I need to filter out the non-approved contracts.

In table 2, I am trying to illustrate what services the supplier is approved to do:

Contract #	Supplier	Arborist	Landscaping	Trash Removal
1	ABC Company	FALSE	TRUE	TRUE
2	Bobs Company	FALSE	FALSE	FALSE
3	XYZ Company	TRUE	FALSE	FALSE

<tbody>
</tbody>

The TRUE/FALSE results are what I am after....

Right now I have the following formula in the TRUE/FALSE cells (C2:E:4) -
=SUMPRODUCT(--ISNUMBER(SEARCH(LookupKey,$A2&";"&C$1)))>0

I think the second part of my search formula is throwing everything off with the text joins, but not sure how else to do this. Maybe it's as simple as a formatting issue?

Thanks for any help in advance!!!!

 

[sheetspread]

Can this be done without the lookup key (everything matched in the sample but perhaps there's more)?

Excel 2010
	A	B	C	D	E
1	Contract #	Supplier	Approved?	Service	LookupKey
2	1	ABC Company	Yes	Trash Removal	1;Trash Removal
3	1	ABC Company	Yes	Landscaping	1; Landscaping
4	2	Bobs Company	No	Landscaping
5	3	XYZ Company	Yes	Arborist	3; Arborist
6
7
8	Contract #	Supplier	Arborist	Landscaping	Trash Removal
9	1	ABC Company	0	1	1
10	2	Bobs Company	0	0	0
11	3	XYZ Company	1	0	0
Sheet30

Cell Formulas
Range	Formula
C9	=SUMPRODUCT(--($B$2:$B$5=$B9),--($D$2:$D$5=C$8),--($C$2:$C$5="Yes"))

 

[steve the fish]

Pasting your first table into A1 of Sheet1 and the second into A1 or Sheet2 use this formula in C2 of Sheet2 and copy down and across:

=IFERROR(IF(LOOKUP(2,1/((Sheet1!$B:$B=$B2)*(Sheet1!$A:$A=$A2)*(Sheet1!$D:$D=C$1)),Sheet1!$C:$C)="Yes",TRUE,FALSE),FALSE)

 

[bondra76]

thanks Steve that worked out great!!!!