Microsoft 365 and Office | Excel | For business | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
Try:
=FILTER(A2#,INDEX(A2#,,2)=D1)
=filter(a2#...
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
If I use the filter formula with a standard reference like =FILTER(A2:C77,B2:B77=D1) It leads to potential spill errors.
Since I am filtering an array can I replace "A2:C77" with the A2# notation? How would the include syntax go to filter off of the B column?
Thx!
Microsoft 365 and Office | Excel | For business | Windows
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
4 answers
Sort by: Most helpful
-
Anonymous
2022-08-25T20:55:23+00:00 -
Anonymous
2022-08-25T20:22:25+00:00 I am not meaning to be difficult but I am not seeing how a screenshot is relevant to my question. I am aware of the spill error but do not want to change the format or layout of the worksheet.
Perhaps, I should ask simply, if writing a formula =FILTER(A2#,What-Comes-Next)
How would I list the condition of the filter to include?
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
-
Anonymous
2022-08-25T18:50:47+00:00 Dear Purchasing1,
Good day! Thank you for posting to Microsoft Community. We are happy to help you.
As per your description, refer to article FILTER function is using an array of criteria as like in your case "A2:C77".
As you mentioned, "If I use the filter formula with a standard reference like =FILTER(A2:C77,B2:B77=D1) It leads to potential spill errors." error occurs when the spill range for a spilled array formula isn't blank or merged cells. Kindly check this article to see if you meet the conditions and how to fix it: How to correct a #SPILL! error
If possible, could you please upload a screenshot of your Excel data sheet view for out reference so we can understand your exact scenario and provide our suggestion in a productive manner, perhaps some related screenshots will be helpful.
Your understanding and patience will be highly appreciated. I hope that you are keeping safe and well!
Sincerely,
Waqas Muhammad