IF AND THEN Formula Multiple Sheets and Entire Column

[GBRBERN]

I have a multi-page spreadsheet in Google Sheets that I'd like to achieve the following:

IF the first name in sheet 1, column A, matches the first name in sheet 2, column B

AND the last name in sheet 1, column B, matches the last name in sheet 2, column A

THEN matched items should show a '2' in column c on sheet 1 (next to the matched name). If no match, then it should be assigned -.

Please note that in my data sheet 1 is called Final Credit (TEST SHEET) and sheet 2 is called 'Tech Toolkit -6/10.'

Here is the formula I'm using: =IF(AND(A:A='Tech Toolkit - 6/10'!B:B,B:B='Tech Toolkit - 6/10'!A:A), "2", "-")

The problem I'm running into is that the formula is not searching the entire column in sheet 2 for matching names.

I can't share the sample spreadsheet for reference, so I hope I explained it well enough.

This is ultimately going to be expanded to dozens of sheets with sheet 1 having columns using whatever code works (hopefully someone can help) pulling from each of the dozens of sheets.

Perhaps I'm approaching this totally wrong and should be using VLOOKUP? If so, how would I use it here?

Thank you!

[FDibbins]

Not too familiar with "sheets", but your formula needs to reference a single cell, not a range - so you are probably using the wrong formula.

Try something like...
=IF(ISERROR(MATCH(A2,Sheet2!$B:$B,0)),"",2)

[GBRBERN]

Not too familiar with "sheets", but your formula needs to reference a single cell, not a range - so you are probably using the wrong formula.

Try something like...
=IF(ISERROR(MATCH(A2,Sheet2!$B:$B,0)),"",2)

Yes! That worked with some minor tweaks with column assignments. Thank you! The code is now:
=IF(ISERROR(MATCH(A:A,'Tech Toolkit - 6/10'!$C:$C,0)),"",2)

The thing is, that matches the last name of the participant, but I also need to match the first name in column B of 'Tech Toolkit - 6/10' as we have 450 participants and many have the same last name so I'll need to match the first, too.

How would I add another match for the second column into the existing formula? Thank you!

[FDibbins]

1. Note that you need to sue a single cell ref (A2), not a range (A:A) for the 1st criteria in the MATCH function

2. Perhaps the simplest way would be to add a helper column to combine the 2 names - then use that column in the MATCH formula (obviously combine the 2 names you are searching for, too)
=IF(ISERROR(MATCH(A2&" "&B2,Sheet2!$J:$J,0)),"",2)
where column J would contain = A2&" "&B2 copied down

[GBRBERN]

1. Note that you need to sue a single cell ref (A2), not a range (A:A) for the 1st criteria in the MATCH function

2. Perhaps the simplest way would be to add a helper column to combine the 2 names - then use that column in the MATCH formula (obviously combine the 2 names you are searching for, too)
=IF(ISERROR(MATCH(A2&" "&B2,Sheet2!$J:$J,0)),"",2)
where column J would contain = A2&" "&B2 copied down

Excellent idea. I merged the first and last name columns and used the MATCH formula against that. Worked like a charm. Now if we could only fix people spelling their names correctly in two places! Thanks for your help.

[FDibbins]

(no need to "reply with quotes" unless you need to refer to a specific reply, just use "reply")

If you have a comprehensive list of all names, you could use data Validation to make sure the names are correct?