Array to find cell data based on two variables

Hi All,

I am looking to do two formula's and I can not seem to get the array's right. I know it is an array and I have scrolled through all the previous help I have had but can not seem to find it so i have come to you all for assistance.

On the template file I have put here I have balnk out all the other workings and sensetive data but here is what i am trying to achieve is to have a one week view of the schedule based on any date range and a list of those who are in (represented by x) on the current day


Formula 1;

In formula sheet range B4:H9 I am trying to create a formula that looks at the persons name in column A and the dates in Row 2 and then match those against the same criteria in sheet two to reveal the corresponding cell values.

Formula 2;

In Column L is there a way to list all persons name who has an X on the the current days date listed in the range B4:H9

It would be a great help if anyone can assist in this one please, it is part of a large allocation project but I can not resolve this by Vlookups and Arrays as hard as I try are not my friend.

Thanks in advance

Danny

(Note to admins, I have previoulsy posted this in catastrophic error wit the data link still in play and it created problems so i have reworded and reasked)

Formula 1

in B4

=IFERROR(INDEX('RAW SCHEDULE'!$C$4:$BV$9,MATCH($A4,'RAW SCHEDULE'!$A$4:$A$9,0),MATCH(B$2,'RAW SCHEDULE'!$C$1:$BV$1,0)),"")

Enter with Ctrl+Shift+Enter (array formula) and then copy across and down

Formula 2

in L4

=IFERROR(INDEX('RAW SCHEDULE'!$C$4:$BV$9,MATCH($A4,'RAW SCHEDULE'!$A$4:$A$9,0),MATCH($M$3,'RAW SCHEDULE'!$C$1:$BV$1,0)),"")

Enter with Ctrl+Shift+Enter (array formula) and then copy down

Formula one is perfect thank you, formula two reveals X's what i was trying to achieve is the list of names who has an X on that specific day

Thanks

Danny

Formula 2

=IFERROR(INDEX('RAW SCHEDULE'!$A$4:$A$9,SMALL(IF(INDEX('RAW SCHEDULE'!$C$4:$BV$9,,MATCH($M$3,'RAW SCHEDULE'!$C$1:$BV$1,0))="X",ROW($A$4:$A$9)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

Enter with Ctrl+shift+Enter

Sorry about error .. senior moment!!

Another option without an array formula.

Formula:

Please Login or Register  to view this content.

Ford,
You're right re Formula 1- my mistake.

Too many senior moments : age catching up !

John

Thats brilliant guys, these formulas were adapted slightly to encompass the whole sheet but it worked great. With formula two currently looking at X is there a way to adapt it so that the match is either X or N

If the values can only be "X" or "N" then we could test for "not blank" ....

=IFERROR(INDEX('RAW SCHEDULE'!$A$4:$A$9,SMALL(IF(INDEX('RAW SCHEDULE'!$C$4:$BV$9,,MATCH($M$3,'RAW SCHEDULE'!$C$1:$BV$1,0))<>"",ROW($A$4:$A$9)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

Thats brilliant but what I have done is kept the X and N into two different columns because of what they represent. I just want to say a massive thank you to you guys and all the others that have help over the time. what this excel file does is truley amazing from my point of view and it couldnt of been done without the help & tuition of the Excelforum Community