CODEWITHSUNDEEP
excelexcel-formula
Balaji Kartheeswaran
Balaji Kartheeswaran

Reputation: 1098

Excel Syntax Error

I can't find the syntax error in the following Excel formula:

I have data in K7:M11 in three sheets namely s_core1,s_core2,s_core3. The search keys are in A6:C6, A7:B7 and so on. If A6 is core1, the data in sheet s_core1 should be used to lookup. If it is core2, the data in sheet s_core2 should be used to lookup.

=INDEX(INDIRECT("s_"&A6&"!"&"$K$11:$M$11"),MATCH(C6,INDIRECT("K"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6&":M"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6),0))

Please help me with that.

Here is the source for reference if needed:

Please refer the attached excel file in the forum:

http://www.excelforum.com/excel-formulas-and-functions/868258-look-up-in-different-sheets.html?p=2968043#post2968043

Thanks in advance.

Upvotes: 1

Views: 354

Answers (1)

barry houdini
barry houdini

Reputation: 46331

You aren't referring to the sheet in the second INDIRECT function - add the bolded part in place of "K"

=INDEX(INDIRECT("s_"&A6&"!"&"$K$11:$M$11"),MATCH(C6,INDIRECT("s_"&A6&"!K"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6&":M"&MATCH(B6,INDIRECT("s_"&A6&"!$J$7:$J$9"),0)+6),0))

Upvotes: 2

Related Questions