Hello Excel Gurus,
Given the Dynamic Array Functions, I am wondering if there's a way to do a spill result on rows and columns, so that the formula updates dynamically, instead of changing it manually?
What I meant by that is, in the past, below are the steps that I'd usually do:
- Create a MATCH Function at cell B2
- Copy the formula on cell B2 and paste it from B2:M9
- Copy and Paste/Delete formula depending on the size (going down the row and/or going across the column)
Thus my question now is, how do I put a '1' when it's found dynamically?
SpillRangeRowsAndColumns.png
Note that picture shows as a spill range for cell A2# and B1#.
I have attached a workbook, but it's not the formulated solution...instead, it's a desired solution that I've manually input.
Bookmarks