Guidance On How To Set A Cell To Y/N Based Upon Multiple Criteria In Another Worksheet

I have two worksheets. I'm would like to populate Column D in the All Ports sheet with Y/N based upon whether or not that swtich/port combination exists in the NAC Ports sheet. So for example, D1 - D4 would have an "N" while D24 - D26 would have a "Y" since those rows exist in the NAC Ports sheet.

Example sheets are below.

I'm pretty much an Excel newbie when it comes to anything beyond basic formulas. Any help along with an explanation as to how the formula works would be much appreciated!

Thanks,

Drew

All Ports Sheet

	A	B	C	D
1	Switch	Port	VLAN Description	NAC
2	CL-First-Floor-Pavillion-Sw	ge-0/0/0	Guest-Wired
3	CL-First-Floor-Pavillion-Sw	ge-0/0/1	Guest-Wired
4	CL-First-Floor-Pavillion-Sw	ge-0/0/2	Guest-Wired
5	CL-First-Floor-Pavillion-Sw	ge-0/0/3	Guest-Wired
...
24	CL-First-Floor-Pavillion-Sw	ge-0/0/11	First-Floor-Users
25	CL-First-Floor-Pavillion-Sw	ge-0/0/12	First-Floor-Users
26	CL-First-Floor-Pavillion-Sw	ge-0/0/13	First-Floor-Users

NAC Ports Sheet

	A	B
1	Switch	Port
2	CL-First-Floor-Pavillion-Sw	ge-0/0/11
3	CL-First-Floor-Pavillion-Sw	ge-0/0/12
4	CL-First-Floor-Pavillion-Sw	ge-0/0/13

you could just use a countIFS

=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2)
where sheet 1 is the name of the NAC sheet

that will give a 0, 1 or more depending on how many matches it finds

then we can use and IF
=IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2)>0, "Y","N")

You can use VLOOKUP or INDEX/MATCH or you could probably get away with a simple COUNTIF.

Formula:

Please Login or Register  to view this content.

Ah, apologies ... missed the combo requirement. So, as etaf has suggested, use COUNTIFS. You don't actually need the >0 part.

You're welcome. Thanks for the rep.