Formula -Multiple criteria return

Jess D.

I need some help determine what the best formula would be to use. In Sheet 1 I want to create a Duplicate column that will check the candidate name in Sheet 1 against the candidate name in Sheet 2, but I only want it to look at candidate names that are not "Engineering" in BUS/ENG. Any formula suggestions for this?

Sheet 1

Column: Candidate Name

Sheet 2

Column: Candidate Name

Column: BUS/ENG (Dropdown- Business, Business Function(Engineering), Engineering, Engineering(Non-EDTM))

Matt Lynn ACT

@Jess D. There's multiple variations of how you could do this. Here's one:

=IF(COUNTIFS({Sheet2CanidateNameColumn}, [Sheet 1 Candidate Name]@row, {Sheet2BusEngColumn}, <>"Engineering") > 0, "Match", "No Match")

Matt Lynn ACT

@Jess D.
When you say "check the… name … against the .. name" are you saying the formula should tell you if there's a match? Reference a value on the row if there is a match? Expand on that please.

It's prob going to be a =index(collect or a =index(match or possibly a if() around one of those.
Example: If I want to know something such as "Is there a match" I may do a =index(collect()) with a =iferror() around it so if there's not an match it would error so I'd get "No match" rather than an error. Example of that formula:
=iferror(index(collect({Sheet1CandidateName},{Sheet1CandidateName},[Candidate Name]@row,{BUS/ENG},@cell<>"Engineering"),1),"No Match")

Give me some more context and I can confirm/test it for you.

Jess D.

@Matt Lynn ACT

My apologizes I should have been clearer. Yes, I am looking to see if the candidate names match. However, I only want to look at those that are not Engineering in the BUS/ENG Column.

I did test out what you sent, it kicks back the #Unparseable error. When I used the AI formula help it is saying @cell in a COLLECT function is not supported, so that maybe why.

Appreciate your help.

Matt Lynn ACT

@Jess D. There's multiple variations of how you could do this. Here's one:

=IF(COUNTIFS({Sheet2CanidateNameColumn}, [Sheet 1 Candidate Name]@row, {Sheet2BusEngColumn}, <>"Engineering") > 0, "Match", "No Match")

Jess D.

@Matt Lynn ACT

That worked! Thanks so much.