index function working correctly when I hit F9, but returns a different result

[pdxexceluser]

Hello,
Goal of project: I'm compiling a list of names on a separate tab from a column that only lists the names sporadically.
Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should. Any thoughts? Thanks.

=IF(ROWS(F$5:F5)<=$F$2,INDEX(Details!B$1:B$39,SMALL(IF(Details!$A$1:$A$39=$F$1,ROW(Details!$A$1:$A$39)-ROW(Details!$A$1)+1),ROWS(F$5:F5))),"")

 

[circledchicken]

Hi,

Try pressing CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula.

 

[T. Valko]

Hello,
Goal of project: I'm compiling a list of names on a separate tab from a column that only lists the names sporadically.
Problem: In the formula, the IF provides a TRUE, the row function, and consequently, the INDEX function (when I hit F9) provides the correct "name" and row number, but when I hit return, it provides the text 4 cells beneath what it should. Any thoughts? Thanks.

=IF(ROWS(F$5:F5)<=$F$2,INDEX(Details!B$1:B$39,SMALL(IF(Details!$A$1:$A$39=$F$1,ROW(Details!$A$1:$A$39)-ROW(Details!$A$1)+1),ROWS(F$5:F5))),"")

Have you entered the formula as an array?

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[pdxexceluser]

Thank you both. The Control/Shift/Enter works. I'm wondering if there is a work-around such that my drag and drop works instead of pressing C/S/E for every row? Much appreciated.

 

[pdxexceluser]

Hi. Thank you. It works. I'm wondering if you know how to get the formula to work such that I can drag and drop as opposed to pressing C-S-E for every row? Much appreciated.

 

[Aladin Akyurek]

Thank you both. The Control/Shift/Enter works. I'm wondering if there is a work-around such that my drag and drop works instead of pressing C/S/E for every row? Much appreciated.

You don't need to apply control+shift+enter (CSE) for every cell. Just once in the cell from which you drag it down. However, when you edit/modify the formula, you agan need to apply CSE. To recap: Just once CSE then copy to other cells.

And, there is no viable and as effcient an alternative native formula...

 

[T. Valko]

Thank you both. The Control/Shift/Enter works. I'm wondering if there is a work-around such that my drag and drop works instead of pressing C/S/E for every row? Much appreciated.

=IF(ROWS(F$5:F5)<=$F$2,INDEX(Details!B$1:B$39,SMALL(IF(Details!$A$1:$A$39=$F$1,ROW(Details!$A$1:$A$39)-ROW(Details!$A$1)+1),ROWS(F$5:F5))),"")

I'm assuming you enter the first formula in cell F5 then you copy it down the column?

If so, all you need to do is array enter the formula in cell F5. As you drag copy the formula it will automatically be entered as an array.

2:30 AM

 

[pdxexceluser]

Thank you. You are both correct. Yeah. Done.