Google sheets, vlookup, iferror formula in a script as a function

0
votes

I have the below formula that populates data from another sheet into one of the columns. The problem is that I have to allow access between the sheets for the below to work. Unfortunately with the IFERROR statement I don't see the "Allow access to the sheet" popup. So i have to remove IFERROR and re-add it manually each time.

Is there a way to use vlookup without IFERROR? Or could I put this formula as a function using the script ?

=ARRAYFORMULA(LOWER(IFERROR(IF(C1:C="test",VLOOKUP(A1:A,IMPORTRANGE("sheet_id","Master !L:N"),3,false),hosts!L3:L))))

many thanks

1
google-sheetsformulavlookuparray-formulas

1 Answers

1
votes

That is NOT the problem with your formula. (actually it seems to work just fine)

The way the IMPORTRANGE function works is that you only have to "Allow access ONCE.

Once access is granted you don't have to do it again. The two sheets are connected for eternity :)

Tip:

Instead of trying to grand access from inside your formula, try the following

On another cell (any empty cell will do) use just the IMPORTRANGE formula:

=IMPORTRANGE("sheet_id","Master !L:N")

You will be asked to give access. Allow it.
Now the sheets are connected and your formula works as expected.