0

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.

Here is a sample data sheet for reference: https://docs.google.com/spreadsheets/d/1q3R5yRdxn3DmDaNjb8WLf7wztZQESTTqA20C8ghYMP0/edit?usp=sharing

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!

Improve this question

1 Answer 1

Reset to default
1

You can use the VLOOKUP approach by using this formula in Sheet1

=ArrayFormula(IF(LEN(IFERROR(VLOOKUP((IF(A2:A= "","", A2:A&"✦"&B2:B)), (IF(Sheet22!B2:B="","", Sheet22!B2:B&"✦"&Sheet22!A2:A)), 1, 0)))=0, "", 2))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the response. I'm getting this error when putting the formula in the cells in the C column: Array result was not expanded because it would overwrite data in C2, C3, C4, etc. There's no data in those cells. If I need to change the sheet the data is pulling from, which part of the formula would I update? I didn't expect to see Sheet22 when I was naming the sheets previously so that confused me. Thank you again!
You can change Sheet22 in the formula above with the sheet name that you have. Also try deleting column C and then adding the formula again to column C, just in case there is something that you are not seeing in the column. Are you doing this in the sheet that you have shared above?
Unfortunately, it's not the same sheet as the one above. The one I'm using has a bunch of PII so I can't share it publically. The model is exactly the same, however. I also found this formula works: =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. Excel formulas can really make your head hurt!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.