formula to lookup values across multiple sheets that doesn't slow down my application

[Pete B]

I'm a newbie to writing formulas for excel, Is there a more eloquent way to write this formula below so that it doesn't continually slow down the workbook. It seems to continually look for data and you cant enter anything until it is done.

The worksheet the formula is on posts students exam scores from other monthly exam score worksheets. It matches the student name and exam name on the month worksheet and returns the score. there is a total of 14 month worksheets and 12 exams it looks for. I notice that having it go through all the cells may be whats slowing it down, but when I change ex. July EOR'!C:C to be specific to a range, it doesnt return anything.

Appreciate everyone's help, expertise and smarts! Thanks

=IFERROR(INDEX('JULY EOR'!C:C,MATCH(1,('JULY EOR'!A:A=A4)*('JULY EOR'!B:B=$B$2),0)),IFERROR(INDEX('AUGUST EOR'!C:C,MATCH(1,('AUGUST EOR'!A:A=A4)*('AUGUST EOR'!B:B=$B$2),0)),IFERROR(INDEX('SEPTEMBER EOR'!C:C,MATCH(1,('SEPTEMBER EOR'!A:A=A4)*('SEPTEMBER EOR'!B:B=$B$2),0)),IFERROR(INDEX('OCTOBER EOR'!C:C,MATCH(1,('OCTOBER EOR'!A:A=A4)*('OCTOBER EOR'!B:B=$B$2),0)),IFERROR(INDEX('NOVEMBER EOR'!C:C,MATCH(1,('NOVEMBER EOR'!A:A=A4)*('NOVEMBER EOR'!B:B=$B$2),0)),IFERROR(INDEX('DECEMBER EOR'!C:C,MATCH(1,('DECEMBER EOR'!A:A=A4)*('DECEMBER EOR'!B:B=$B$2),0)),IFERROR(INDEX('JANUARY EOR'!C:C,MATCH(1,('JANUARY EOR'!A:A=A4)*('JANUARY EOR'!B:B=$B$2),0)),IFERROR(INDEX('FEBRUARY EOR'!C:C,MATCH(1,('FEBRUARY EOR'!A:A=A4)*('FEBRUARY EOR'!B:B=$B$2),0)),IFERROR(INDEX('MARCH EOR'!C:C,MATCH(1,('MARCH EOR'!A:A=A4)*('MARCH EOR'!B:B=$B$2),0)),IFERROR(INDEX('APRIL EOR'!C:C,MATCH(1,('APRIL EOR'!A:A=A4)*('APRIL EOR'!B:B=$B$2),0)),IFERROR(INDEX('MAY EOR'!C:C,MATCH(1,('MAY EOR'!A:A=A4)*('MAY EOR'!B:B=$B$2),0)),IFERROR(INDEX('JUNE EOR'!C:C,MATCH(1,('JUNE EOR'!A:A=A4)*('JUNE EOR'!B:B=$B$2),0)),IFERROR(INDEX('JULY 2 EOR'!C:C,MATCH(1,('JULY 2 EOR'!A:A=A4)*('JULY 2 EOR'!B:B=$B$2),0)),IFERROR(INDEX('AUGUST 2 EOR'!C:C,MATCH(1,('AUGUST 2 EOR'!A:A=A4)*('AUGUST 2 EOR'!B:B=$B$2),0))," "))))))))))))))

 

[vletm]

Pete B​

Could You send a sample Excel-file with some sample data and expected results?
Why ... there should be 14 month worksheets instead of one?

 

[p45cal]

You're looking at whole columns in 14 sheets. I don't imagine you have about 1 million rows of data in each month. In the following formula, still inefficient, I've assumed 100 rows per month, and that you have a version of Excel with the functions LET, CHOOSECOLS, TAKE and VSTACK. It should produce the same results as your current formula.

=LET(allABC,VSTACK('JULY EOR'!$A$1:$C$100,'AUGUST EOR'!$A$1:$C$100,'SEPTEMBER EOR'!$A$1:$C$100,'OCTOBER EOR'!$A$1:$C$100,'NOVEMBER EOR'!$A$1:$C$100,'DECEMBER EOR'!$A$1:$C$100,'JANUARY EOR'!$A$1:$C$100,'FEBRUARY EOR'!$A$1:$C$100,'MARCH EOR'!$A$1:$C$100,'APRIL EOR'!$A$1:$C$100,'MAY EOR'!$A$1:$C$100,'JUNE EOR'!$A$1:$C$100,'JULY 2 EOR'!$A$1:$C$100,'AUGUST 2 EOR'!$A$1:$C$100),
INDEX(TAKE(allABC,,-1),MATCH(1,(A4=TAKE(allABC,,1))*($B$2=CHOOSECOLS(allABC,2)),0)))

That formula would go in a cell in row 4 probably.
If you have BYROW available you could have a version of the same formula that spills down into multiple rows:

=BYROW(A4:A8,LAMBDA(a,LET(allABC,VSTACK('JULY EOR'!$A$1:$C$100,'AUGUST EOR'!$A$1:$C$100,'SEPTEMBER EOR'!$A$1:$C$100,'OCTOBER EOR'!$A$1:$C$100,'NOVEMBER EOR'!$A$1:$C$100,'DECEMBER EOR'!$A$1:$C$100,'JANUARY EOR'!$A$1:$C$100,'FEBRUARY EOR'!$A$1:$C$100,'MARCH EOR'!$A$1:$C$100,'APRIL EOR'!$A$1:$C$100,'MAY EOR'!$A$1:$C$100,'JUNE EOR'!$A$1:$C$100,'JULY 2 EOR'!$A$1:$C$100,'AUGUST 2 EOR'!$A$1:$C$100),
INDEX(TAKE(allABC,,-1),MATCH(1,(a=TAKE(allABC,,1))*($B$2=CHOOSECOLS(allABC,2)),0)))))

Note that this one will spill down into 5 cells because of the A4:A8 in it.

I do NOT recommend using either of these formulae. Instead, do as @vletm suggests and attach a workbook.