Hi there,

This is a little bit more in depth than my last question. It is an amalgamation of two previous questions. The following is a formula I have created=IF(AS3=0,0,INDEX(INDIRECT("'"&AS3&"'!A5:ZZ26),(MATCH(AS7,(INDIRECT("'"&AS3&"'!A5:ZZ5),0)+11),(MATCH(AQ5,(INDIRECT("'"&AS3&"'!A8:ZZ26),0)+12)))))

AS3 = the name of a worksheet

A5:ZZ26 is the array within that worksheet

AS7 = Date A5:ZZ26 is the row in which the date can be found

AQ5 = value to look forexample of the answer Date is found in N5

Value found in L8

Returned value = P8Where have I gone wrong with this formula??????

Without testing anything, I'll take a shot.. The INDIRECT function is looking for a text representative of a range reference. Therefore, all parts of the reference need to be inside the quotes so that INDIRECT sees it as test.

It doesn't look like you have closed the quotes after the second & in any of your INDIRECT functions.

Maybe something like this...

INDIRECT("'"&AS3&"'!A5:ZZ5")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

=INDEX(INDIRECT("'"&AS3&"'!M8:ZZ26"),(MATCH(AS7,(INDIRECT("'"&AS3&"'!N5:ZZ5")),0)+2),(MATCH(AQ10,(INDIRECT("'"&AS3&"'!L8:L26")),0))) Match AS7 comes up with 241+2 = 243 Column IV

Match AQ10 comes up with 1 Row 8However the answer comes as #REF!

I cannot see why this is occurring, any ideas??

Obviously without your data, it's hard to test your formula. That said, just from reading your post, maybe I'm missing something but consider this...

The syntax of the INDEX function is:

=INDEX(array, row_num, [column_num])

Breaking down your formula, I get:

array: INDIRECT("'"&AS3&"'!M8:ZZ26"), row_num: MATCH(AS7,(INDIRECT("'"&AS3&"'!N5:ZZ5")),0)+2, column_num: MATCH(AQ10,(INDIRECT("'"&AS3&"'!L8:L26")),0))In your post you said:

"Match AS7 comes up with 241+2 = 243 Column IV"

and

"Match AQ10 comes up with 1 Row 8"

While I'm not quite sure what you mean by "1 Row 8", it looks to me like perhaps your matches are reversed. You don't have 243 Rows in your array, so Excel is telling you that you are referencing a cell that is outside of your array.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

=IF(AS3="",0,IF(AS7>DASHBOARD!$R$1,0,INDEX(INDIRECT("'"&AS3&"'!$M$8:ZZ26"),(MATCH(AQ10,(INDIRECT("'"&AS3&"'!$L$8:L26")),0)),(MATCH(AS7,(INDIRECT("'"&AS3&"'!$N$5:ZZ5")),0)+3)))) Finally cracked the code with the help of DerbyDad03, I seem to be doing silly things and not getting the right answer. Thank the stars you are at the other end. I think I know understand the INDEX,Match criteria.

Once again many thanks for your assistance

Ask Your Question

Weekly Poll