Array Formula Help w/ Excel Workbook Example

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.

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).