Two conditions - One criteria in Row and another in Column

[Rive]

Hi - I have one table of data with three columns (A=Component Description; B=Activity Description; C=Role Name).

My task is to identify what Role has access to what activity with the output being a table of unique Activities, with relative component Descrpitions next to it and unique role names along the top row.

In worksheet two of this example, I have listed the unique reports and I have transposed a list of unique roles along the top row.

I want to create lookups that will return either a YES or NO in the cell for a particular activitiy for the particular role?

Can you please help?

[rylo]

Hi

Try

=IF(SUMPRODUCT(--('source data'!$B$2:$B$3687=results!$A2),--('source data'!$C$2:$C$3687=results!C$1)),"Yes", "No")

in results!C2 and copy down / across as required.


rylo

[Rive]

Thanks Rylo -- I was excited as the first cell returned the correct result, however upon coping the formula all other cells return a #value.

I made sure I identified my absolutes, but still no luck.

any thoughts?

[rylo]

Hi

I took the formula as I gave it and put it into results!C2 of your example file, then copied and pasted down / across and it worked OK.

Can you cut down the size of your file and see if it works with a smaller sample? If not, then put up your much smaller sample file, including the formula and I'll have a look.


rylo