1

So I would like to create a function or something that returns absolutely nothing in a cell. When I say nothing, I mean that if the cell before returns an array of value, it can write in this cell and doesn't returns #REF (Cannot expand results).

The idea is that I have a function sort() that get me a list of keys. Then I retrieve the values with a filter function like so :

=FILTER(B$2:B$7, A$2:A$7=D2)

But sometimes the keys (which are dates) can be duplicated, and that makes that the FILTER function with return 2 times 2 rows, creating a #REF error (cannot expand results).

If I create a condition :

=IF(D1<>D2, FILTER(/*...*/), "")

The second cell is empty but I still get the #REF error, because it's not really empty.

Is there a way to make that work ?

player0
  • 124,011
  • 12
  • 67
  • 124
vinalti
  • 966
  • 5
  • 26
  • No. There's nothing like that. Possible duplicate of https://stackoverflow.com/a/46884012/ `ARRAY_CONSTRAIN` is the only way here. – TheMaster Oct 13 '22 at 10:28
  • Can you share an example sheet with data, your actual result and the expected result? – Kessy Oct 19 '22 at 15:48

1 Answers1

2

to create a function or something that returns absolutely nothing in a cell

try:

=IFERROR(0/0)

or:

=IF(;;)

but what you actually need is:

=UNIQUE(FILTER(B$2:B$7, A$2:A$7=D2))

or:

=INDEX(FILTER(B$2:B$7, A$2:A$7=D2), 1)

or:

=ARRAY_CONSTRAIN(FILTER(B$2:B$7, A$2:A$7=D2), 1, 1)
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    I could fix the issue by combining different functions that you provided. So I used the `FILTER` function to get all the corresponding lines, and the `INDEX` function together with a `COUNTIF()` in order to return the correct line. – vinalti Oct 20 '22 at 08:49