PDA

View Full Version : Help with INDEX formula



metwerks
2017-10-01, 07:28 PM
I am struggling with a index formula shown below. Player Master is the WS name I am looking up and trying to index by player ranking (column G) if there is a T associated with their name in column V. I have a similar formula working in another workbook and I copied everything the same and changed only the WS name and columns/rows and "T" criteria

This works - See file
=IFERROR(INDEX(Roster!A$2:A$83,SMALL(IF(Roster!$C$2:$C$83="A",Roster!$A$2:$A$83),ROWS($1:1))),"")


This DOES NOT work and it tries to open "Update Values:Master" folder
=IFERROR(INDEX(Player Master!G$3:G$103,SMALL(IF(Player Master!$V$3:$V$103="T",Player Master!$G$3:$G$103),ROWS($1:1))),"")

I would appreciate any help

Bob Phillips
2017-10-01, 10:57 PM
Names with a space in them have to be quote-enclosed

=IFERROR(INDEX('Player Master'!G$3:G$103,SMALL(IF('Player Master'!$V$3:$V$103="T",'Player Master'!$G$3:$G$103),ROWS($1:1))),"")

metwerks
2017-10-02, 03:06 PM
Names with a space in them have to be quote-enclosed

=IFERROR(INDEX('Player Master'!G$3:G$103,SMALL(IF('Player Master'!$V$3:$V$103="T",'Player Master'!$G$3:$G$103),ROWS($1:1))),"")

Thanks - It helped with error, but it is not loading the way I want. I want it to pick the highest ranking player with the T association and put them in new order. In the example I have on new WB "LMFUTURE", I want 1,2,5 & 6 ranked on WS Player Master to be loaded into Roster WS C2 C3 C4 C5 and so on

I have tried changing ROWS($1:1) to 2:2, 1:2 2:1 with no luck

NBVC
2017-10-02, 04:35 PM
The formula you are using is an Array Formula (https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7). You must confirm the formula with CTRL+SHIFT+ENTER before copying down.

metwerks
2017-10-02, 05:00 PM
Thank you

p45cal
2017-10-02, 07:12 PM
I think you need to commit the formula to the cell with Ctrl+SHift+Enter, not just Enter.
You migh even get away with:
=IFERROR(SMALL(IF('Player Master'!$V$3:$V$103="T",'Player Master'!$G$3:$G$103),ROWS($2:2)),"")
entered in the same way.

Whichever you choose, you should copy the formula down the column as far as you need.