Index Match across multiple tabs

[seraja27]

Hi, I am working on an index-match function. When I do it on the tab with the data it works fine, but when I do it on another tab it does not work...my formula is as follows...

{=INDEX('Sheet1'!AB30:AB92,MATCH(A6&B6,'Sheet1'!C30:C92&'Sheet1'!B30:B92,0))}

 

[XOR LX]

Hi,

Can you explain in words precisely what it is you are trying to achieve?

Regards

 

[seraja27]

I am trying to return a value from Sheet1 which is a number classified by month (month is the heading...so each month has its own column). on the tab I am entering the formula into, I want to match the values in cells A6&B6 to the values in the ranges for C30:C92 & B30:B92 which contains the values in cells A6 & B6.

 

[XOR LX]

And you've manually verified that there are actual matches for your concatenated strings? What result do you get? Can you show me the precise formula as it looks in the sheet that doesn't work?

Regards

 

[seraja27]

yes I have. the formula is as follows...

=INDEX('Sheet1'!AB30:AB92,MATCH(A6&B6,'Sheet1'!C30:C92&'Sheet1'!B30:B92,0))

answer should be 65

When I did the same formula on the sheet with the data as follows it works...

=INDEX(AB30:AB92,MATCH(AP28&AQ28,C30:C92&B30:B92,0))

 

[XOR LX]

Thanks.

I noticed that the formula in your original post had curly brackets around it. Was that just your way of indicating that the formula in question is an array formula? Did you ensure that your new, copied version was also committed in the same way?

Regards

 

[seraja27]

you're welcome.

if it don't put in the brackets {} then I receive a #VALUE!

 

[XOR LX]

When you say "put in the brackets", I hope you don't mean that you're manually adding them to the formula.

Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

Regards

 

[seraja27]

yes I use ctrl+shift+enter

I don't put them in manually

 

[seraja27]

I think I may know what the issue is. will merged cells affect the calculation? Cell A6, A7, A8 are merged (so the reference cell would be A6...B6 is not merged.