Solved: Help with array formula

[lacviet2005]

Hi all,
I need your help on the attached spreadsheet. Please take a look.

Is this something for arrays function? :dunno

Thank you.

[Bob Phillips]

Put this formula in F21

=IF(ISERROR(SMALL(IF(INDEX($C$4:$R$12,0,--MID($F$19,FIND(" ",$F$19)+1,99)+1)=1,ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1,""),ROW($A1))),"",
INDEX($B$4:$B$12,SMALL(IF(INDEX($C$4:$R$12,0,--MID($F$19,FIND(" ",$F$19)+1,99)+1)=1,ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1,""),ROW($A1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

Then copy it down to F35.

[lacviet2005]

Thank you very much for you help. :bow:

[lucas]

Need your help is not a very descriptive title for your thread and is useless for searches. I have changed it for you to reflect the content of the thread.

[lacviet2005]

Thanks Lucas.

I know this thread has been solved, but I want to understand the formula that xld posted above. What does it mean?
($C$4:$R$12,0,--MID($F$19,FIND(" ",$F$19)+1,99)+1)=1

Thanks,
LV

[Bob Phillips]

Which part do you not understand, or is it all of it?

[lacviet2005]

I tried to change the column headers to something else (e.g. from Day 1, Day 2 to D1, D2, ect.), and the formula didn't work anymore. I want to understand how many characters you allow in your formula.

What does "($C$4:$R$12,0,--MID($F$19,FIND(" ",$F$19)+1,99)+1)=1" mean?

Thanks xld.
LV

[Bob Phillips]

You need to look at a bit more than just this,

the_day_num: --MID($F$19,FIND(" ",$F$19)+1,99)+1)

is used to get the day number from your data validation list in F19, having to strip off the trailing number

test_array: INDEX($C$4:$R$12,0,the_day_num)

These values is then used in the INDEX function as the column number to get an array of values for that particular day

check_array: IF(test_value = 1,ROW($B$4:$B$12)-MIN(ROW($B$4:$B$12))+1,"")

this tests the array of values for that particular day building an array of row numbers where the value is 1

[Shazam]

Here is another formula you could use.

=INDEX($B$4:$B$12,SMALL(IF(INDEX($C$4:$R$12,0,MATCH($F$19,$C$2:$R$2,0))=1,R OW($B$4:$B$12)-ROW($C$4)+1),ROWS($G$21:G21)))

or if you dont like to see any errors then try this formula below.

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($B$4:$B$12,SMALL(IF(INDEX($C$4:$R$12,0,MATCH($F$19,$C$2:$R$2,0))=1,R OW($B$4:$B$12)-ROW($C$4)+1),ROWS($G$21:G21)))))

[lacviet2005]

You guys are AWESOME!!!

Thanks so much.