# Solved Find specific data within another worksheet

September 16, 2013 at 14:49:08
Specs: Windows 7
 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 worksheetA5:ZZ26 is the array within that worksheetAS7 = Date A5:ZZ26 is the row in which the date can be foundAQ5 = 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??????

See More: Find specific data within another worksheet

#1
September 16, 2013 at 15:00:02
 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")

Report •

#2
September 17, 2013 at 10:38:33
 =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 IVMatch AQ10 comes up with 1 Row 8However the answer comes as #REF!I cannot see why this is occurring, any ideas??

Report •

#3
September 17, 2013 at 11:27:19
 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.