Match Two Criteria in Two DIFFERENT Spreadsheets NOT in Column 1, return lookup value

%3CLINGO-SUB%20id%3D%22lingo-sub-3402654%22%20slang%3D%22en-US%22%3EMatch%20Two%20Criteria%20in%20Two%20DIFFERENT%20Spreadsheets%20NOT%20in%20Column%201%2C%20return%20lookup%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3402654%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(ISNA(MATCH(A2%2C'FY2122%20Split%20Gifts%20to%20Reconfigure%205.19.2022.csv'!%24A%3A%24A%2C0))%2C%22%22%2CVLOOKUP(A2%2C'FY2122%20Split%20Gifts%20to%20Reconfigure%205.19.2022.csv'!%24A%3A%24G%2C2%2CFALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20above%20formula%20to%20search%20in%20a%20different%20sheet%2C%20match%20and%20return%20a%20value%20in%20a%20worksheet%20that%20matches.%20It%20works%20great%20for%20one%20data%20point.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThe%20first%20file%2C%20the%20contact%20ID%20shows%20up%20one%20time%20in%20column%20A.%20In%20the%20second%20file%2C%20it%20can%20show%20many%20times%20in%20Column%20A%20for%20each%20row%20that%20matches.%20In%20the%20first%20file%2C%20there%20is%20another%20criteria%20in%20Column%20H%20that%20needs%20to%20be%20matched%20to%20Column%20D%20values%20the%20second%20file%2C%20when%20these%20two%20match%20then%20return%20the%20value%20in%20File%202%20into%20Column%20B%20in%20file%201.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20figure%20out%20how%20to%20do%20both%20criteria%20statements.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%201%20-%20V%20Lookup%20Returns%20the%20ID%20from%20the%20second%20file%20but%20only%20the%20first%20instance%20of%20the%20matching%20field%20in%20column%20A%3C%2FP%3E%3CTABLE%20width%3D%22386%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22135.938px%22%20height%3D%2258px%22%3EGift%20Import%20ID%3C%2FTD%3E%3CTD%20width%3D%22136.348px%22%20height%3D%2258px%22%3EVLookUP%20Formula%3C%2FTD%3E%3CTD%20width%3D%22112.461px%22%20height%3D%2258px%22%3EFund%20ID%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135.938px%22%20height%3D%2258px%22%3E00001-545-0000100139%3C%2FTD%3E%3CTD%20width%3D%22136.348px%22%20height%3D%2258px%22%3E00001-553-0000103752%3C%2FTD%3E%3CTD%20width%3D%22112.461px%22%20height%3D%2258px%22%3EGIK%20Event%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135.938px%22%20height%3D%2258px%22%3E00001-545-0000100139%3C%2FTD%3E%3CTD%20width%3D%22136.348px%22%20height%3D%2258px%22%3E00001-553-0000103752%3C%2FTD%3E%3CTD%20width%3D%22112.461px%22%20height%3D%2258px%22%3EGIKFOOD%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22135.938px%22%20height%3D%2258px%22%3E00001-545-0000100139%3C%2FTD%3E%3CTD%20width%3D%22136.348px%22%20height%3D%2258px%22%3E00001-553-0000103752%3C%2FTD%3E%3CTD%20width%3D%22112.461px%22%20height%3D%2258px%22%3EGIKHousehold%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%202%20I%20need%20to%20match%20both%20values%20in%20both%20files%20so%20that%20if%20the%20GFImpID%20and%20the%20GSSPlitFund%20match%20in%20both%20files%20then%20return%20the%20GSSplitImpID%20into%20the%20first%20file.%26nbsp%3B%20GFImpID%20is%20a%201%3A%20MANY%20with%20GSplitImpID%3C%2FP%3E%3CTABLE%20width%3D%22386%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22145%22%3EGFImpID%3C%2FTD%3E%3CTD%20width%3D%22145%22%3EGSplitImpID%3C%2FTD%3E%3CTD%20width%3D%2296%22%3EGSplitFund%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00001-545-0000100139%3C%2FTD%3E%3CTD%3E00001-553-0000103752%3C%2FTD%3E%3CTD%3EGIK%20Event%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00001-545-0000100139%3C%2FTD%3E%3CTD%3E00001-553-0000103751%3C%2FTD%3E%3CTD%3EGIKFOOD%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00001-545-0000100139%3C%2FTD%3E%3CTD%3E00001-553-0000103753%3C%2FTD%3E%3CTD%3EGIKHousehold%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00001-545-0000100140%3C%2FTD%3E%3CTD%3E00001-553-0000103755%3C%2FTD%3E%3CTD%3EGIK%20Event%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3402654%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3402714%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20Two%20Criteria%20in%20Two%20DIFFERENT%20Spreadsheets%20NOT%20in%20Column%201%2C%20return%20lookup%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3402714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1395095%22%20target%3D%22_blank%22%3E%40MovesMC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DVLOOKUP(A2%26amp%3BC2%2CCHOOSE(%7B1%2C2%7D%2C'FY2122%20Split%20Gifts'!%24A%242%3A%24A%245%26amp%3B'FY2122%20Split%20Gifts'!%24C%242%3A%24C%245%2C'FY2122%20Split%20Gifts'!%24B%242%3A%24B%245)%2C2%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

=IF(ISNA(MATCH(A2,'FY2122 Split Gifts to Reconfigure 5.19.2022.csv'!$A:$A,0)),"",VLOOKUP(A2,'FY2122 Split Gifts to Reconfigure 5.19.2022.csv'!$A:$G,2,FALSE))

 

I am using the above formula to search in a different sheet, match and return a value in a worksheet that matches. It works great for one data point. 


The first file, the contact ID shows up one time in column A. In the second file, it can show many times in Column A for each row that matches. In the first file, there is another criteria in Column H that needs to be matched to Column D values the second file, when these two match then return the value in File 2 into Column B in file 1.  

 

I cannot figure out how to do both criteria statements. 

 

File 1 - V Lookup Returns the ID from the second file but only the first instance of the matching field in column A

Gift Import IDVLookUP FormulaFund ID
00001-545-000010013900001-553-0000103752GIK Event
00001-545-000010013900001-553-0000103752GIKFOOD
00001-545-000010013900001-553-0000103752GIKHousehold

 

File 2 I need to match both values in both files so that if the GFImpID and the GSSPlitFund match in both files then return the GSSplitImpID into the first file.  GFImpID is a 1: MANY with GSplitImpID

GFImpIDGSplitImpIDGSplitFund
00001-545-000010013900001-553-0000103752GIK Event
00001-545-000010013900001-553-0000103751GIKFOOD
00001-545-000010013900001-553-0000103753GIKHousehold
00001-545-000010014000001-553-0000103755GIK Event

 

Thank you!

1 Reply

@MovesMC 

=VLOOKUP(A2&C2,CHOOSE({1,2},'FY2122 Split Gifts'!$A$2:$A$5&'FY2122 Split Gifts'!$C$2:$C$5,'FY2122 Split Gifts'!$B$2:$B$5),2,0)

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.