Using VLOOKUP with two lookup values while using IMPORTRANGE to merge three Google Sheets-CodePudding

I have created three google sheets wherein one google sheet is designed as the Masterfile for the data present on the two other google sheets.

I was able to merge two google sheets data into the Masterfile using the formula below:

=QUERY({IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs","Sheet1!A2:H");IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0","Sheet1!A2:H")},"WHERE Col2 IS NOT NULL ORDER BY Col1 ASC")

However, what I want to do right now is to import data in another using IMPORTRANGE while using VLOOKUP based on two lookup values. Here's the formula I've used on that specific column and it returns me an error of "VLOOKUP evaluates to an out of bounds range":

=ARRAYFORMULA(VLOOKUP(A2&" "&B2, {IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", {Sheet1!$A$2:$A&" "&Sheet1!$B$2:$B,Sheet1!$F$2:$F});IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0", {Sheet1!$A$2:$A&" "&Sheet1!$B$2:$B,Sheet1!$F$2:$F})},5,false))

I was able to test the same formula while only using one lookup value please see the working formula below for only one lookup value:

=ARRAYFORMULA(VLOOKUP(A2, {IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!A2:F");IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0", "Sheet1!A2:F")},6,false))

CodePudding user response：

multiranges are not supported within importrange. use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2&" "&B2, {
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!A2:A")&" "&
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!B2:B"),
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!F2:F")}, 2, )))

for two importranges it would be:

=ARRAYFORMULA(IFNA(VLOOKUP(A2&" "&B2, {
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!A2:A")&" "&
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!B2:B"),
 IMPORTRANGE("1JY2OMkBa926_P_PJABMxFqctfnRQAPAknkvMzFn7lFs", "Sheet1!F2:F"); 
 IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0", "Sheet1!A2:A")&" "&
 IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0", "Sheet1!B2:B"),
 IMPORTRANGE("12I5uRLkS_YjgWPdf_Kx5orQxX876XruW9Q9kDrECJi0", "Sheet1!F2:F")}, 2, )))

also make sure that you run each unique importrange first to connect your sheets by allowing access before you use any of above formulae