Asking the experts why a formula working in Google Sheets fails to work in an Excel file that has been downloaded? Here is the formula working in Sheets: =IFERROR(Query('TAB1'!A2:K,"Select A,C,D,J,K where G <>'' ",1),"No records found")Does Excel require something different in the syntax? The formula looks for a filled in cell in G column in TAB1, then transfers the entire row of data into TAB2 where the formula is hiding. This works flawlessly in Google Sheets. However, it does not work as it is shown in the downloaded Excel file. When I open the downloaded file with Excel, I see this message "We found a problem with some content in 'Your_File.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." Also the formula in the downloaded Excel version has changed to this: =IFERROR(__xludf.DUMMYFUNCTION("IFERROR(Query('TAB1'!A2:K26,""Select A,C,D,J,K where G <>'' "",1),""No records found"")"),"Standard")

mtarler jenniferk007 I can't replicate the error on a Mac. As far as I can see there nothing special about any of the formulas in this tread that would cause an error on a Mac. All of the functions are supported in that version if I recall correctly. The only thing I can think of is that you get the message box line in the picture below.As you know, this shows up on a PC as well if you are using the wrong list separator, for instance. If that's not it, can you jenniferk007 upload a screenshot showing us what exactly is going on. Even better if you could upload or share the file.

jenniferk007 Going back to you original question and mtarler 's first response. Google Sheets and Excel are NOT the same. When you say that you open a Google sheets document in Excel, fair chance that Excel doesn't know what to do with it. Query, select and where are not an Excel functions, hence the "__xludf.DUMMYFUNCTION" part that Excel puts into the formula. You have to re-write it with valid Excel functions. I believe that's what Mat tried to do for you. Your Excel for Mac version 16.57 should support all the functions he used. But you can check that by entering =FILT or =SEQU. Excel should prompt the FILTER or SEQUENCE function. If not, then your version can't use Mat's formula.

jenniferk007 Possibly (You may adjust the ranges to accommodate your data):=LET(f,FILTER('TAB1'!A2:K5,'TAB1'!G2:G5<>" "),INDEX(f,SEQUENCE(ROWS(f)),{1,3,4,10,11}))

basically google sheet and MS excel are not the same. QUERY doesn't exist in MS excel. Based on the context I would try something like:=FILTER('TAB1'!A2:K26, 'TAB1'!G2:G26<>"", "No records found")That will return all the columns but if you need only columns A,C,D,J,K then try:=FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")

Thank you for the reply, but I am getting a "The function isn't valid"error message on the following... the inner parenthesis are lighting up, the set inside of INDEX.=FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")

Convert from Google Sheets using IFERROR not working

mtarler
Silver Contributor
Jul 27, 2022
basically google sheet and MS excel are not the same. QUERY doesn't exist in MS excel. Based on the context I would try something like:
=FILTER('TAB1'!A2:K26, 'TAB1'!G2:G26<>"", "No records found")
That will return all the columns but if you need only columns A,C,D,J,K then try:
=FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")
- jenniferk007
  Copper Contributor
  Jul 28, 2022
  Thank you for the reply, but I am getting a "The function isn't valid"error message on the following... the inner parenthesis are lighting up, the set inside of INDEX.
  =FILTER(INDEX('TAB1'!A2:K26,SEQUENCE(25,1,2),{1,3,4,10,11}), 'TAB1'!G2:G26<>"", "No records found")
  - mtarler
    Silver Contributor
    Jul 28, 2022
    jenniferk007 I'm not sure what is the issue. I did find a small error but that just caused it to miss a line not give the error you mentioned. here is an example in the attached. I also updated it to use LET() and set the "in" range and sequence automatically on that. but picking the columns {1,2, ...} on line 4 and the column to check on line 5 is still manual:
    =LET(in,A2:G16, FILTER(INDEX(in, SEQUENCE(ROWS(in)), {1,3,4,6,7}), G2:G16<>"", "No records found"))
    I should ask, do you have Excel 365? If you type =FIL do you see FILTER in the pop up suggestion window?
    Can you attach a sample file and maybe a screenshot of the error.
    Filter-Example.xlsx10 KB
Patrick2788
Silver Contributor
Jul 27, 2022
jenniferk007
Possibly (You may adjust the ranges to accommodate your data):
=LET(f,FILTER('TAB1'!A2:K5,'TAB1'!G2:G5<>" "),INDEX(f,SEQUENCE(ROWS(f)),{1,3,4,10,11}))

Forum Discussion

Convert from Google Sheets using IFERROR not working

Share

Resources