'How can I solve the"FILTER has mismatched range sizes" error in the formula below
I get the filter has mismatched range error when I insert this: Data!N3:N <> "", "No Market")
Formula in Google Sheets
=FILTER(Data!B3:N,
Data!C3:C>=B1,
Data!C3:C<=D1,
Data!N3:N <> "", "No Market")
I'm trying to replace the blank values in Column N with the text "No Market"
Sample Table
| Column B | Column N |
|---|---|
| 03/07/2021 | New York |
| 03/07/2021 | |
| 03/07/2021 | Seattle |
| 04/04/2021 | |
| 04/04/2021 | Boston |
This formula also worked, but it excludes the blank values and I would like to include them.=FILTER(Data!B3:N,
Data!C3:C>=B1,
Data!C3:C<=D1,
Data!N3:N <> "")
| Column B | Column N |
|---|---|
| 03/07/2021 | New York |
| 03/07/2021 | Seattle |
| 04/04/2021 | Boston |
Expected Results:replaces blank values with "No Market"
| Column B | Column N |
|---|---|
| 03/07/2021 | New York |
| 03/07/2021 | No Market |
| 03/07/2021 | Seattle |
| 04/04/2021 | No Market |
| 04/04/2021 | Boston |
I appreciate your help, thanks in advance!
Solution 1:[1]
Try below formula-
=ArrayFormula({A2:A6,IF(B2:B6="","No Market",B2:B6)})
Solution 2:[2]
It's difficult to write full formulas without access to any actual data, but this should work for you:
=ArrayFormula(IFERROR(FILTER({Data!B3:M, IF(Data!N3:N="", "No Market", Data!N3:N)}, Data!C3:C<>"", Data!C3:C>=B1, Data!C3:C<=D1)))
If it does not work as expected, consider sharing a link to the spreadsheet.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Harun24hr |
| Solution 2 | Erik Tyler |