Forum Discussion

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

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

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
- mtarler
  Silver Contributor
  Aug 01, 2022
  What version of Excel are you using?
  - jenniferk007
    Copper Contributor
    Aug 01, 2022
    I am using Excel 16.57 on a Macbook.