Log In
Register
Home
Active Member
June 1, 2022
Offline
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?
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
Active Member
June 1, 2022
Offline
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
July 31, 2020
Offline
I think the problem is the Choose function.
Just use
=FILTER(Data!$A$2:$B$16,Data!$C$2:$C$16=$C$1)
Active Member
June 1, 2022
Offline
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.
July 31, 2020
Offline
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}))
Answers Post
1 Guest(s)
