Create formula that can fetch the data on criteria

In C5, enter this array formula with Ctrl + Shift + Enter keys together and drag + fill the formula from C5 to G5.
=INDEX(Data!$C$3:$M$28,MATCH(Report!$E$1,Students,0),MATCH(C3&C4,Data!$C$1:$M$1&Data!$C$2:$M$2,0))

Originally Posted by JieJenn

In C5, enter this array formula with Ctrl + Shift + Enter keys together and drag + fill the formula from C5 to G5.
=INDEX(Data!$C$3:$M$28,MATCH(Report!$E$1,Students,0),MATCH(C3&C4,Data!$C$1:$M$1&Data!$C$2:$M$2,0))

Thank you Mr JieJenn But Iwant 3 formulas not one:
1- formula to fetch the lecturer
2-formula to fetch the lectures
3-formula to fetch dates
Your formula needs to be edited :
I don`t want the dates formula to depend on the lecturer and lectures data
Thanks

Keep the array formula just extend the range. Anyway, give this code a try.

Please Login or Register  to view this content.

My knowledge in Excel is insufficient, hopefully someone else will be able to assist you.

When I change student name to"C" or any other name,I found the formula didn`t fetch date field.
Please note that my data sheet may expand to 1000 column and more than 100 students.

1- formula to fetch the lecturer
2-formula to fetch the lectures
3-formula to fetch dates

While I suppose one could argue that the below formulas for Lecturer and Lectures are the same formula with different INDEX 1st arguments it works because the layout of the data permits it.

Try array entering this in C3. Then fill down and across to G4. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Then array enter this in C5 and fill across.

Formula:

Please Login or Register  to view this content.

Will this work?

You have to change my Solution or the Solution of FR, if You have more Columns as in the above Example...

Don't forget to enter this array formula with Ctrl+Shift+Enter...

PS. I like Hurgada...

leprince2007,

I did this part

COLUMN($C:$M)-MIN(COLUMN($C:$M))+1 to allow flexibility. Should you insert / deleted columns to the left of the formula this "resets" the first column number to 1 and does it without need for editing. That shouldn't slow the workbook. The same is true of COLUMNS($C:C).

The likely culprit in the slow down are the whole column references such as in MATCH(Report!$E$1,Data!$B:$B,0).


Try replacing the formulas in post #12 with these. They are still array entered. In C3 then filled down and across to G4

Formula:

Please Login or Register  to view this content.

Then array enter this in C5 and fill across.

Formula:

Please Login or Register  to view this content.

The reason for selecting row 1000 is to make adequate room for additional rows.

If 1000 is not enough edit the formulas to include a little more than what you would anticipate. ie if anticipated need is 500 make it 750 or more. The main thing to avoid with exact matches is referencing whole columns.

My apologies for the oversight.

You are welcome. Thank you for the feedback and the rep.