Forum Discussion

user_1214's avatar
user_1214
Copper Contributor
Nov 19, 2021

Can't Export Google Sheets to Excel

 

I am trying to import a google sheets file to excel,  but my formulas are not translating.

I know arrayformula does not work with Excel, but I cannot find the equivalent.

 

My formula in Google Sheets is   =IFERROR(sort(unique(arrayformula(VLOOKUP(A1:A,B1:B,1,FALSE)))),"")

 

What is showing up in Excel is =IFERROR(@__xludf.DUMMYFUNCTION("IFERROR(sort(unique(arrayformula(VLOOKUP(A1:A1442,B1:B1442,1,FALSE)))),"""")"),"Both")

 

Thank You in advance!

 

excel
Excel for web
office 365
training

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 19, 2021

    user_1214 

    As variant that could be

    =SORT( UNIQUE( FILTER(B1:B1442, COUNTIF(A1:A1442,B1:B1442) ) ) )

    VLOOKUP() is not the best option working with dynamic arrays

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 19, 2021
    Array functionality is in Excel 365 so just drop "arrayformula" and try again. Should be something like:
    =IFERROR(sort(unique(VLOOKUP(A:A,B:B,1,FALSE))),"")

Resources