INDEX/MATCH with multiple criteria and dynamic worksheet reference

Hi all,

Real stuck on an Excel problem and looking for some advice please. I think this might just be a syntax issue so I have just provided formulas. If it would help to have a data snippet, please let me know.

What I am trying to do is have an INDEX/MATCH function with two MATCH criteria and dynamic worksheet references. I have used the INDIRECT function to achieve the dynamic references where possible but not married to this if there is a better way.

I have searched on old posts looking for an answer but no luck so far. Not sure if I am just looking for the wrong keywords.

This is my starting formula which works fine:

Please Login or Register  to view this content.

This is what I would like to achieve:

Please Login or Register  to view this content.

But whenever I try and replace the flat worksheet references in the multiple criteria MATCH function with this second formula, the array errors out with a #REF!.

I don't understand why the INDIRECT function works fine in the first part of the formula but errors out in the second half? Is it because I am trying to return an array? Am I trying to go about this in a really dumb way?

Real stuck on an Excel problem and looking for some advice please. I think this might just be a syntax issue so I have just provided formulas. If it would help to have a data snippet, please let me know.

Yes. That is almost always helpful, and saves time.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.