View Full Version : Solved: Help with array formula
lacviet2005
07-16-2007, 03:27 PM
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
07-16-2007, 03:41 PM
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
07-16-2007, 04:56 PM
Thank you very much for you help. :bow:
lucas
07-16-2007, 05:04 PM
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
07-17-2007, 01:32 PM
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
07-17-2007, 03:57 PM
Which part do you not understand, or is it all of it?
lacviet2005
07-17-2007, 04:21 PM
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
07-18-2007, 12:44 AM
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
07-18-2007, 07:10 AM
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
07-18-2007, 02:50 PM
You guys are AWESOME!!!
Thanks so much.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.