Search code examples
google-sheetsmismatchifs

IFS formula returns #VALUE! which says IFS has mismatched range sizes. How to fix?

=IFS(AND(B15="",C24="",C25="",C28="",C29=""),"",
AND(B15="",C24="",C25="",C28="",C29<>""),FILTER(Database,Salary<=C29),
AND(B15="",C24="",C25="",C28<>"",C29=""),FILTER(Database,Salary>=C28),
AND(B15="",C24="",C25="",C28<>"",C29<>""),FILTER(Database,Salary>=C28,Salary<=C29),
AND(B15="",C24="",C25<>"",C28="",C29=""),FILTER(Database,HiringDate<=C25),
AND(B15="",C24="",C25<>"",C28="",C29<>""),FILTER(Database,HiringDate<=C25,Database,Salary<=C29),
AND(B15="",C24="",C25<>"",C28<>"",C29=""),FILTER(Database,HiringDate<=C25,Salary>=C28),
AND(B15="",C24="",C25<>"",C28<>"",C29<>""),FILTER(Database,HiringDate<=C25,Salary>=C28,Salary<=C29),
AND(B15="",C24<>"",C25="",C28="",C29=""),FILTER(Database,HiringDate>=C24),
AND(B15="",C24<>"",C25="",C28="",C29<>""),FILTER(Database,HiringDate>=C24,Salary<=C29),
AND(B15="",C24<>"",C25="",C28<>"",C29=""),FILTER(Database,HiringDate>=C24,Salary>=C28),
AND(B15="",C24<>"",C25="",C28<>"",C29<>""),FILTER(Database,HiringDate>=C24,Salary>=C28,Salary<=C29),
AND(B15="",C24<>"",C25<>"",C28="",C29=""),FILTER(Database,HiringDate>=C24,HiringDate<=C25),
AND(B15="",C24<>"",C25<>"",C28="",C29<>""),FILTER(Database,HiringDate>=C24,HiringDate<=C25,Salary<=C29),
AND(B15="",C24<>"",C25<>"",C28<>"",C29=""),FILTER(Database,HiringDate>=C24,HiringDate<=C25,Salary>=C28),
AND(B15="",C24<>"",C25<>"",C28<>"",C29<>""),FILTER(Database,HiringDate>=C24,HiringDate<=C25,Salary>=C28,Salary<=C29),
AND(B15<>"",C24="",C25="",C28="",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17),
AND(B15<>"",C24="",C25="",C28="",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,Salary<=C29),
AND(B15<>"",C24="",C25="",C28<>"",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,Salary>=C28),
AND(B15<>"",C24="",C25="",C28<>"",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,Salary>=C28,Salary<=C29),
AND(B15<>"",C24="",C25<>"",C28="",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate<=C25),
AND(B15<>"",C24="",C25<>"",C28="",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate<=C25,Salary<=C29),
AND(B15<>"",C24="",C25<>"",C28<>"",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate<=C25,Salary>=C28),
AND(B15<>"",C24="",C25<>"",C28<>"",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate<=C25,Salary>=C28,Salary<=C29),
AND(B15<>"",C24<>"",C25="",C28="",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24),
AND(B15<>"",C24<>"",C25="",C28="",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,Salary<=C29),
AND(B15<>"",C24<>"",C25="",C28<>"",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,Salary>=C28),
AND(B15<>"",C24<>"",C25="",C28<>"",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,Salary>=C28,Salary<=C29),
AND(B15<>"",C24<>"",C25<>"",C28="",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,HiringDate<=C25),
AND(B15<>"",C24<>"",C25<>"",C28="",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,HiringDate<=C25,Salary<=C29),
AND(B15<>"",C24<>"",C25<>"",C28<>"",C29=""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,HiringDate<=C25,Salary>=C28),
AND(B15<>"",C24<>"",C25<>"",C28<>"",C29<>""),FILTER(Database,LEFT(CHOOSECOLS(Database,MATCH(B15,13:13,0)),LEN(B17))=B17,HiringDate>=C24,HiringDate<=C25,Salary>=C28,Salary<=C29))

Error: IFS has mismatched range sizes. Expected row count: 1, column count: 1. Actual row count: 989,column count:29. How do I fix this? I'm filtering an employee database, and set up an interactive search filters where users can input their desired filters and my formula should return a range of employee information such as hire date, salary, other info, etc.

I tried reading about IFS mismatched range sizes questions and answer here and over the web, but I can't apply what they've said to my formula.

Solution

  • Try adapting your ifs() version into simple if(); the below is a sample excerpt of how it should be framed:

    =IF(AND(B15="",C24="",C25="",C28="",C29=""),"",
    IF(AND(B15="",C24="",C25="",C28="",C29<>""),FILTER(Database,Salary<=C29),
       IF(AND(B15="",C24="",C25="",C28<>"",C29=""),FILTER(Database,Salary>=C28),
          IF(AND(B15="",C24="",C25="",C28<>"",C29<>""),FILTER(Database,Salary>=C28,Salary<=C29)))))
    • Here's some possible explanation on ifs() backend behavior which usually leads to the mismatched range sizes error