Help with Cross-Criteria in Multiple Columns

[HelpMeWithExcelPlease]

Hi all,

I'm hoping there is a simple answer to this that I just haven't been able to think of. I simplified my issue into the two tables below, the first being my raw data table and the second being the basic reporting dashboard that I am building out.

Raw Data Table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]State 1[/TD]
[TD]State 2[/TD]
[TD]State 3[/TD]
[TD]Impact?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]California[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Colorado[/TD]
[TD]Kansas[/TD]
[TD]California[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Maine[/TD]
[TD]New York[/TD]
[TD]Colorado[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Reporting Dashboard:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Impact?[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Maine[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

The formula I do not know how to create is whatever formula will fill out my "Impact?" column on my reporting dashboard. Is it a VLOOKUP with conditions? An IF Statement? Any help on this issue would be greatly appreciated, thanks in advance!

 

[jarjarbingie]

Hi all,

I'm hoping there is a simple answer to this that I just haven't been able to think of. I simplified my issue into the two tables below, the first being my raw data table and the second being the basic reporting dashboard that I am building out.

Raw Data Table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]State 1[/TD]
[TD]State 2[/TD]
[TD]State 3[/TD]
[TD]Impact?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD][/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]California[/TD]
[TD]Texas[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Colorado[/TD]
[TD]Kansas[/TD]
[TD]California[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Maine[/TD]
[TD]New York[/TD]
[TD]Colorado[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Reporting Dashboard:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Impact?[/TD]
[/TR]
[TR]
[TD]Texas[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Kansas[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Maine[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

The formula I do not know how to create is whatever formula will fill out my "Impact?" column on my reporting dashboard. Is it a VLOOKUP with conditions? An IF Statement? Any help on this issue would be greatly appreciated, thanks in advance!

assuming table 1 is in Sheet1 and table 2 is in Sheet2:

B2 = IF(SUM(IF((Sheet1!$B$2:$D$5=$A2)*(Sheet1!$E$2:$E$5="Yes"),1,0))>0,"Yes","No") press CTRL+SHIFT+ENTER