Array Formula Help w/ Excel Workbook Example

Here's one way. It returns "Product 4/FY 20". I modified how your array formula works

Select all of an FY column, (in the sample, select E3 through E30) and use Control + Shift + Enter to enter this Array Formula. Copy entire column of formulas through the rest of the columns as needed.

=IFERROR(INDEX($A$3:$A$30,SMALL(IFERROR(1/(E$2=$B$3:$B$30)*ROW()+1,""),ROW(1:100))-3)&"/"&INDEX($C$3:$C$30,SMALL(IFERROR(1/(E$2=$B$3:$B$30)*ROW()+1,""),ROW(1:100))-3),"")

I am not sure if that is what you needed, let me know

Interpreting your request differently, this is messy but it works
(entered as an array)

Formula:

Please Login or Register  to view this content.

I believe ChemistB's solution does just that, see the post above your last

Here is another slightly easier to follow alternative (No offense, ChemistB!), again entered into the entire column at the same time then copied across:


=IFERROR(INDEX($A$3:$A$30,IFERROR(SMALL(IFERROR(--CONCATENATE(IFERROR(1/--(E2=$B$3:$B$30)*ROW(1:30),""),IFERROR(1/--(E2=$C$3:$C$30)*ROW(1:30),"")),""),ROW(1:30)),"")),"")

Formula edited, i had the references off slightly.

No offense taken. I often take the long road to get to a solution.

And if you modify your formula slightly (locking in references and change the final ROW(1:30) to ROWS($1:1), you can enter it as an array in E3 and then drag down and right instead of the need for multiple cell array.

=IFERROR(INDEX($A$3:$A$30,IFERROR(SMALL(IFERROR(--CONCATENATE(IFERROR(1/--(E$2=$B$3:$B$30)*ROW($1:$30),""),IFERROR(1/--(E$2=$C$3:$C$30)*ROW($1:$30),"")),""),ROWS($1:1)),"")),"")

More Condensed formula.. ( Inspired from Sir ChemistB )

Formula:

Please Login or Register  to view this content.

Thank you ChemistB for letting us know such a beautiful construction...
check the attached file...

Originally Posted by mcmahobt

Speshul,

Would you be able to further explain how the ROW(1:30) function in your formula works?

I'm trying to make appropriate changes to apply this to my master workbook, and I'm assuming the reason it is not working for me is because of the ROW function having a wrong reference when I try to change it.

The ROW(1:30) effectively increases by 1 per row it is placed on. This formula uses it to get the k value of the small formula. So, starting from the top formula, it returns a 1, the second formula, it is a 2. Giving you the Smallest, then Second smallest, then Third smallest, and so on. The 1:30 is just stating how high to count. It is arbitrary as long as the number is higher than the data that will be needed, since the example had 27 rows, I just picked 30.

If you use the function =ROW() and drag it down, you will be returned with the row number the formula is placed on.

If you define the row number, =ROW(1:10) and copy it down, you will get 1, 2, 3, 4, 5....and so on regardless of where the formula is placed. If you EXCEED the number of rows (10 in this example) you will get #N/A errors. So if you get getting errors using the formula in a larger area than the 30 allowed for by the ROW function, that is likely why.

What you could do is change the formula to
=IFERROR(INDEX($A$3:$A$30,IFERROR(SMALL(IFERROR(--CONCATENATE(IFERROR(1/--(E2=$B$3:$B$30)*ROW()-X,""),IFERROR(1/--(E2=$C$3:$C$30)*ROW()-X,"")),""),ROW()-X),"")),"")

Where X is the row number ABOVE the first row of the formula. So then , if it is on Row 20, the formula is =ROW()-19, which = 1, or the first smallest number. If the formula starts on row 1, you simply need to use =ROW()

Otherwise, you can change it to anything larger than the number of rows you will be using. ROW(1:100000) would even work! (I think!)




As a note, a formula like ChrmistB's solution, which is entered into one cell and copied, would likely use ROWS($A$1:A1). The ROWS function counts the number of rows within the defined range, so $A$1:A1 would be one row, and when you copy it since the first part is locked, it becomes $A$1:A2, counting for two rows.

This type of count wouldn't work in my formula because every cell would return a one, or ROWS($A$1:A1).

Thanks for reputation points, Mcmahobt
I have just shortened it..
Its chemistb who should be honored for this formula..