CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Gregory Bailey
Gregory Bailey

Reputation: 1

IF AND THEN Formula Multiple Sheets and Entire Column

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!

Upvotes: 0

Views: 146

Answers (1)

Broly
Broly

Reputation: 921

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

Upvotes: 1

Related Questions