Vlookup a QR code cell in Google Sheets returns empty

Question

In Google Sheets when searching for data of a row from another tab/sheet based on a Vlookup search, the cell that has a QR code returns empty. EXAMPLE IS HERE: https://docs.google.com/spreadsheets/d/1jy8AVbSfgJRh5WAFNgEOAq-VvEuBbyMDk5js3EiiRf4/edit?usp=sharing

I have 4 tabs/sheets in Google Sheets:

(Sheet1-data) Gathers data from Google Forms.
(Sheet2-process) Serves to process the data from sheet one and is where the QR codes get generated.
(Sheet3-pivot) Gathers all data and adds an N/A automatically to empty cells and it's based on: 'No match' message in Google Sheets Vlookup results for cells with no data.
(Sheet4-search) Performs a Vlookup search based on a value entered by the user and it's based on: Search a value from another tab/sheet in google sheets based on cell reference.

The code for generating the QR in (Sheet2-process) is this:

={"QR";arrayformula( if( len(A2:A),IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=120x120&data="&ENCODEURL(
"-Tipo de Equipo: "&B2:B& char(10)& 
"-ID unico: "& F2:F& char(10)& 
"-# Serial: "&G2:G & char(10)&
"-Area: "&L2:L & char(10)),2)   ,""))}

The code for the QR column in (Sheet3-pivot) is this:

 =arrayformula(IF(LEN('Sheet1-data'!$A:$A), IF(Sheet2-process!M:M<>"",Sheet2-process!M:M,"N/A"),""))

And then, the code for the search is something like this:

=ARRAYFORMULA(
    IF(B3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
        VLOOKUP(B3, {Sheet3-pivot!AN:AN, Sheet3-pivot!A:BR}, 
                    {69,70},0)), CHAR(10))), "♦", ),
    IF(C3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
        VLOOKUP(C3, {Sheet3-pivot!AK:AK, Sheet3-pivot!A:BR}, 
                    {69,70},0)), CHAR(10))), "♦", ), 
    IF(D3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
        VLOOKUP(D3, {Sheet3-pivot!AR:AR, Sheet3-pivot!A:BR}, 
                    {69,70},0)), CHAR(10))), "♦", ),
    IF(E3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
        VLOOKUP(E3, {Sheet3-pivot!W:W, Sheet3-pivot!A:BR}, 
                    {69,70},0)), CHAR(10))), "♦", ),
    IF(F3<>"", SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
        VLOOKUP(F3, {Sheet3-pivot!Z:Z, Sheet3-pivot!A:BR}, 
                    {69,70}, 0)), CHAR(10))), "♦",),
                    ))))))

The search does return all values except for the QR code which would be the row 70 (it's the ...{69,70}... ). The cell just appears empty as if there was nothing in the proper column of the pivot sheet.

The objective of the values searched in (Sheet4-search) is that they can be referenced on a future new sheet/tab with a printable report depending on what was searched.

My only guess so far is that it may be because the QR is an image. However, if for example, I use a simple =BQ3 (A cell with a generated QR), the reference works perfectly.

The example sheet is here: docs.google.com/spreadsheets/d/… — deags

player0 player0 · Accepted Answer · 2019-06-07T22:08:32

you will need to add it in array {} separately like:

=ARRAYFORMULA(
        IF(B3<>"", {SUBSTITUTE(TRANSPOSE(SPLIT(TEXTJOIN(CHAR(10)&CHAR(10)&"♦", 1, 
            VLOOKUP(B3, {'Sheet3-pivot'!AN:AN, 'Sheet3-pivot'!A:BR}, 
                        {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,71},
 0)), CHAR(10))), "♦", ); VLOOKUP(B3, {'Sheet3-pivot'!AN:AN, 'Sheet3-pivot'!A:BR}, 70, 0)},

        IF(C3<>"", {...............

Vlookup a QR code cell in Google Sheets returns empty

1 Answers