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
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
the next thing that comes to my mind is a bit unusual URL you are using try simply: i.stack.imgur.com/CiiGX.png — player0
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
Ready. Added a link to an example in the Question and edited the code to match it. — deags

player0 player0 · Accepted Answer · 2019-08-26T20:24:02

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")

VLOOKUP inside an ARRAY to search another Google Sheets book

1 Answers