Preface: I'm using Excel 2016**
I've got an array formula that is searching through a data set to ultimately find a list (with a dynamic length, hence the offset formula) of values based off of several criteria (hence the array).
The formula works fine and returns what I expect, my issue is more of how I can clearly display it.
{=OFFSET('Fitment Lists'!$C$5,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,COUNTA(OFFSET('Fitment Lists'!$C$5,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,50)),1)}
When solved the formula will return the following list found from a single column
{"result 1";"result 2";"result 3";"result 4";"result 5";"result 6";"result 7";"result 8"}
The way I normally use a formula like this is to give me a dynamic drop down list which would allow all of the values to be displayed in the list. However I'm trying to make a quick summary and would like to have these 8 or n results from the formula displayed in a single cell or in a specified range.
At the moment it's only displaying the first result (as expected) when entered in a cell.
I'm aware that in 365 you can have an array formula spill over into the cells under where the formula is written but I'm not sure of how to make this work in Excel 2016 (or if it's even possible)
The below is what I'm looking to achieve here,
| Row # | Col A |
|---|---|
| 1 | Result 1 ' Formula entered here |
| 2 | Result 2 |
| 3 | Result 3 |
| 4 | Result 4 |
| 5 | Result 5 |
| 6 | Result 6 |
| 7 | Result 7 |
| 8 | Result 8 |
If anyone has any insight I'd be very appreciative.
Thanks,
CodePudding user response：
Before Dynamic Arrays: to return multiple results from a formula you need to select all the cells you want to contain the results, type the formula in the formula bar and then use Control-Shit-Enter to create the array formula. But the result is a static array, not dynamic, so you usually need to enter it into more cells than you actually need. The excess cells get filled with #N/A so usually you would wrap the formula in an IFERROR.
CodePudding user response：
I thought of an alternate solution instead of using an array formula applied to a range of cells.
As I was having trouble with IFERROR for that method still leaving behind some #N/A values, the below tests for how many rows have data in them against the current row copied down.
Rows($A$1:A1)>(COUNTA(OFFSET('Fitment Lists'!$C4,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,50)))
After that I just set the reference in the Offset to only be absolute to for the column reference so that when copied down it would return the next row value. Seen below with "'Fitment Lists'!$C4"
,OFFSET('Fitment Lists'!$C4,1,MATCH(1,(($DH$8='Fitmen...
Original formula
{=OFFSET('Fitment Lists'!$C$5,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,COUNTA(OFFSET('Fitment Lists'!$C$5,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,50)),1)}
Updated formula (Array copied down)
=IF(ROWS($DO$8:$DO8)>(COUNTA(OFFSET('Fitment Lists'!$C$4,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,50))),"",OFFSET('Fitment Lists'!$C4,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,COUNTA(OFFSET('Fitment Lists'!$C4,1,MATCH(1,(($DH$8='Fitment Lists'!$C$2:$MIN$2)*($DI$8='Fitment Lists'!$C$3:$MIN$3)*($DK$7='Fitment Lists'!$C$4:$MIN$4)),0)-1,50)),1))
Thank you @Charles Williams for helping me think about this problem in a new way.