Excel Syntax Error

Question

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.

The syntax looks okay, but can't see the sheet so not sure what is going on. One thing that will cause an error is if any of your MATCH formulas can't find a matching element, resulting in an #N/A which will then cause your INDIRECT functions to fail. Try stepping through the formula with Evaulate Formula in Excel 2007, look under the Formula menu and click Evaluate Formula). Chances are you'll see an error pop up somewhere, and then you should be able to see what is causing it. — RocketDonkey
– RocketDonkey, Commented Oct 14, 2012 at 20:16
I've seen others do it with a public Dropbox account, so that may work. — RocketDonkey
– RocketDonkey, Commented Oct 14, 2012 at 20:23
ziddu.com/download/20607393/trial22.xls.html Can you download and view the file from here? — Balaji Kartheeswaran
– Balaji Kartheeswaran, Commented Oct 14, 2012 at 20:30
Looks like it is due to your last MATCH formula trying to match a value that is not found in your K7:M9 matrix. In order for it to work, you'll need to mess around a bit with the match_type parameter in your function to allow it to accept values that aren't equal to the one you're seeking. Happy to help later on if you need it :) — RocketDonkey
– RocketDonkey, Commented Oct 14, 2012 at 20:37

barry houdini · Accepted Answer · 2012-10-14 21:44:26Z

2

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))

answered Oct 14, 2012 at 21:44

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Balaji Kartheeswaran Over a year ago

Thank you so much @barry... cha, didn't notice it at all... have been struggling for 4 hours.. Thanks again...

ApplePie Over a year ago

When dealing with large functions and having to debug them, I find that using F9 is very helpful to calculate intermediary values and check if they are what I intend them to be. When you select part of a formula and press F9, Excel will replace that expression with the value it evaluates to. Just a quick tip.

Collectives™ on Stack Overflow

Excel Syntax Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related