0

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,

AesusV
  • 39
  • 6

2 Answers2

1

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.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • Thank you Charles! That did the trick. I wasn't aware I could enter formulas in that way, thank you for the info and the solution! – AesusV Jun 06 '22 at 00:45
  • I was playing around with this method and while it does work for showing the additional values in other cells as I wanted, and the IFERROR is able to handle removing the #N/A should there be no first value, when the formula returns values, the extra #N/A errors still remain. I'm also not able to conditionally format them as excel seems to only be looking at the top values and that they aren't returning errors. Any ideas how to fix this? – AesusV Jun 06 '22 at 00:59
  • I thought of an alternate solution where I would use ``` 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,100))) ``` The second half of that being the part of the offset formula that counts the number of rows in the array. This allowed me to get rid of the extra #N/A values that you warned me of. 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. – AesusV Jun 06 '22 at 02:04
0

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.

AesusV
  • 39
  • 6