VLOOKUP inside an ARRAY to search another Google Sheets book

Question

I have a document 'A' where I have a formula in a 'SEARCH' tab to search for a value from another 'ENTRIES' tab in Google Sheets based on a cell reference written by the user in the same 'SEARCH' tab than the formula.

If I duplicate the 'SEARCH' tab in another Google sheets document/book 'B', how should the formula be altered so that it still references 'A'?

The original formula is based on: Search a value from another tab/sheet in google sheets based on cell reference

WORKING EXAMPLE HERE (This would be document 'B' and it tries to reference a search in another document 'A'): https://docs.google.com/spreadsheets/d/1Ffl6IbehI0slLChyuW-MDezF2xwt0rX12JNIaCFvEI8/edit?usp=sharing (You can see in cell B8 the formula with IMPORTRANGE that I'm trying to implement)

And this would be document'A'. Originally it is an example of how to search for values in another tab based in a different cell reference : [https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit?usp=sharing]

I also checked:

Google Sheets VLOOKUP of multiple columns across multiple sheets
VLOOKUP to the left from another sheet in Google Sheets
Docs Editors help: IMPORTRANGE https://support.google.com/docs/answer/3093340

My original formula in book 'A' is:

    =IFERROR(ARRAYFORMULA(
    IF(B3<>"",SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
        VLOOKUP(B3, {data!AN:AN, data!A:BN},  {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ),
    IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
        VLOOKUP(C3, {data!AK:AK, data!A:BN}, {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
    IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
        VLOOKUP(E3, {data!BJ:BJ, data!A:BN}, {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
    IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
        VLOOKUP(D3, {data!R:R, data!A:BN}, {41,38,19,11,55,56}, 0)), 
    CHAR(10))), "♦", ), ))))), "no match found")

I was trying to use : VLOOKUP(search_key, importrange, index, [is_sorted]) to get in an IMPORTRANGE, like:

IFERROR(ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3, {(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!AN:AN"),
                        (IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!AK:AK"),
                        (IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
 IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(E3, {(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!BJ:BJ"),
                        (IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!R:R"),
                        (IMPORTRANGE("https://docs.google.com/spreadsheets/d/1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8/edit#gid=468700626","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), ))))), "no match found")))))))))

It marks as ERROR, but even if I change it to be ... VLOOKUP(B3{(IMPORTRANGE("URL","data!R:R"),(IMPORTRANGE("URL","data!A:BN")}, ... that gets into () both references, it still marks ERROR.

did you allow access between sheets from importrange before you run it the arrayformula? — player0, Aug 23 '19 at 22:23
Yes. Access is set to 'anyone with the link' and just to be sure I added a range in a new tab just to check that it indeed imported the data. I think the problem is somewhere in the formula on where/how I'm adding the import range. — deags, Aug 26 '19 at 15:30
the next thing that comes to my mind is a bit unusual URL you are using try simply: https://i.stack.imgur.com/CiiGX.png — player0, Aug 26 '19 at 15:40
The URL Im using is one that google gives you when sharing a link. I still get ERROR. VG: `=ARRAYFORMULA( IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, VLOOKUP(B3, {(IMPORTRANGE("https://docs.google.com/spreadsheets/d/ALPHANUMTEXT/edit#gid=135622###","pivot_r!AN:AN"), (IMPORTRANGE("https://docs.google.com/spreadsheets/d/ALPHANUMTEXT/edit#gid=1356222###","pivot_r!A:BN")}, {24,3,21,23,14,5,6,9,10,67,53,54,55,56,57,58,59,30,61,27,62,63,64,65,41,38,36,37,31,32,33,34,45}, 0)), CHAR(10))), "♦", ), ...` — deags, Aug 26 '19 at 18:36
Ready. Added a link to an example in the Question and edited the code to match it. — deags, Aug 26 '19 at 19:39

score 2 · Accepted Answer · answered Aug 26 '19 at 20:24

Pharse Error is caused by extra parenthesis which breaks the formula. FX should be:

=IFERROR(ARRAYFORMULA(
 IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(B3, {IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!AN:AN"),
                         IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ),
 IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(C3, {IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!AK:AK"),
                         IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
 IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(E3, {IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!BJ:BJ"),
                         IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), 
 IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&"♦"&CHAR(10)&"♦", 1, 
            VLOOKUP(D3, {IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!R:R"),
                         IMPORTRANGE("1qLcJdCn4EdV7lPOAfZ_CMak1LBkve45FL5SXyqBV3L8","data!A:BN")},
                        {41,38,19,11,55,56}, 0)), CHAR(10))), "♦", ), ))))), "no match found")

I added the working example on the question. Also and to sum for anyone who may require an explanation. `IMPORTRANGE` does NOT need the full URL that google gives you for sharing the link. ; just the part between the /d/ and the /edit#gid . Also there was an extra `( ` right before `IMPORTRANGE` that shouldn't have been there. — deags, Aug 27 '19 at 15:47

VLOOKUP inside an ARRAY to search another Google Sheets book

1 Answers1