INDEX and MATCH
I have a registration/attendance form which contains list of people that attended the the occasion from day1 through day 5. If anyone attended or missed any day, I put "P" for present or "A" for Absent under the each day i.e
Day1 Day2 Day3 Day4 Day5
P A P P P
Now, I want to find for each day, the list of people with "P". I used this formula: =INDEX(Registration!B10:B159,MATCH("P",Registration!N10:N159,0))
On the first list for Day1, the first person on Row 1 (Larry) was present, the second person on Row 2 Kimberly was absent, the third person on Row 3 (Daniel) was present, Row 4 (Samuel) was present, and so on. Upon using the above formula, I got the name of the 1st person who was present, it skipped the 2nd person since she was absent but it put the name of the 3rd person in the 2nd and 3rd row. That is:
Larry
Daniel
Daniel
Samuel
I want the formula not to repeat names, but to skip to next as long as the person is absent. I need help.
Thank you.
not sure what you want but...
Ok so lookups like index and match will search your your array till it finds the "Match" condition and return the first value, so in this case as you have not fixed the starting cell the array will always start it's search from the current row. So when in Row 1 it finds a P in row 1 and returns the name Larry, in row 2 there is no P so it carries on through the array, checking the next row 3 where it finds P and then returns the name from row 3 being Daniel.
To avoid this you could add an If function to the begining as follows:
=if(N10<>"P","",index(registration!B10:B159,match("P",Registration!N10:N159,0)))
Alternately you can simplify this by getting rid of the lookup as you don't seem to need that:
=if(N10<>"P","",B10)
Also if you wish more varied options you would probably be better using a pivot table as you can then manipulate the infomation you wish to view very quickly & easily
Regards
Mark