Forum Discussion

jenniferk007's avatar
jenniferk007
Copper Contributor
Jul 27, 2022

Convert from Google Sheets using IFERROR not working

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,...
excel
Formulas and Functions
mtarler's avatar
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's avatar
    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's avatar
      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.

      • jenniferk007's avatar
        jenniferk007
        Copper Contributor
        Aug 01, 2022

        Still in using this code, I get the "That function isn't valid" error:

         

        =LET(in,A2:G16,FILTER(INDEX(in,SEQUENCE(ROWS(in)),{1,3,4,6,7}),G2:G16<>"","No records found"))

         

        mtarler 

Resources