I am trying to get some values based on a selection criteria using the FILTER function. While the FILTER function works perfectly with the criteria I have provided I seem to be facing an issue when it comes to some of the cell values in the spill. What I observed is that the character length in these particular cells are greater than 255 and therefore excel seems to be giving the #VALUE! error message. Is this a known limitation and is there a work around for this?
Hi Sean,
There can be workarounds, but we need to understand first how are you using the results to be able to provide an alternative.
Can you describe what you need with more details?
Thank you
Hi Catalin,
Thanks for responding. I've attached a file with some random data. Due to confidentiality I cant upload my working file but the attachment replicates the issue I'm facing.
Cheers,
Sean
I think the problem is the Choose function.
Just use
=FILTER(Data!$A$2:$B$16,Data!$C$2:$C$16=$C$1)
The thing is I have multiple columns of which the data doesn't need to appear as well as multiple selection criteria that is required hence the reason to use the CHOOSE function. I was at the initial step of setting this up when this issue came up.
Sorry for the delay in replying, I only got a notification today.
The formula you are trying to use if only filtering columns A & B based on col C, so there is no need to use choose.
If you want to display non-contiguous columns you can do that like
=LET(f,FILTER(Data!$A$2:$E$16,Data!$C$2:$C$16=$C$1),INDEX(f,SEQUENCE(ROWS(f)),{1,2,5}))
