INDEX/MATCH based on 4 criteria, 2 vertical, 2 horizontal

Hi All,
I am struggling to adapt my working INDEX/MATCH that is working on 3 criteria (1 Vertical + 2 Horizontal).

Please Login or Register  to view this content.

I have looked all over the forums & google and found a couple of blog & forum posts that seem to address the issue I am having, but when I try and adapt my formula, I get #ref or #value errors.
My data is arranged in a table with 2 headings on the X axis & 2 on the Y.
excel snip.PNG

This is what I have:

Please Login or Register  to view this content.

Any ideas or advice on how to address this, or a better way of achieving the same thing would be great!

Thanks.

Please post a small sample file showing expected outcome(s).

Hi John, Thanks for the reply, here is some sample data. I now have a formula that isn't throwing an error, but is giving the wrong answer. Formula is found in cell "AE5".

Try

=INDEX(C$5:Z10,MATCH(1,(AE4=A5:A10)*(AD4=B5:B10),0),MATCH(1,(AC4=C4:Z4)*(AB4=C3:Z3),0))

Answer 228 (?)

You had the row and column MATCH the wrong way round (for the INDEX) and you needed the AND (*) condition on one of the MATCHes.

Thank you! Yes that is the correct answer. Thank you for your help. I seem to have been frustratingly close on a few of my previous attempts, but couldn't get the syntax correct. Thank you very much. ( I can stop banging my head against the wall )

Thank you for the feedback.

Hi there!

I was using this thread to apply some formulas. I have a similar situation with two horizontal lookups and one vertical lookup. The only major issue is that I need a formula which I can drag across and down. The sample file provided can be used to explain my problem, except only one column. I know this post is quite old, but I am hoping someone would assist me with this problem. I'm sure there is a way to figure this out!

Looking forward to hearing back from you!

Thank you,
Nidhi