Problems with Index Match formula

[erasmusthesis]

Hi everyone,

Today is my first day on Excel Forum. Thank you for this great forum.

Currently I have some problems with an index match formula with 3 criteria. Trying to include my excel worksheet, but the attachment function is not working. I use the following formula:
=INDEX($S$3:$AC$1130,MATCH($E3,$O$3:$O$1130,0),MATCH($F3,$S$2:$AC$2,0),MATCH($G2,$Q$3:$Q$1130,0))

I get the correct data for H2. However, if I want to obtain data based on H2 instead of G2, I receive an #REF! error. How can I fix this?

Hopefully is someone willing to help.

Thank you in advance!

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[erasmusthesis]

Hope this works

[Glenn Kennedy]

Your data looks badly corrupted (Cols O &P have a lot of error messages). Also you chose 2011, where there is only one valid answer. So I changed the year to 2012.

So. Your formula had two row criteia and one column criterion. The row criteria have to be combined.

=IFERROR(INDEX($S$3:$AC$1000,MATCH(1,INDEX(($O$3:$O$1000=$E3)*($Q$3:$Q$1000=$H$2),0),0),MATCH($F3,$S$2:$AC$2,0)),"")

copied down. You may need ; as the separator.

[erasmusthesis]

Thanks for your help! Appreciate it a lot

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[erasmusthesis]

Thank you for your message Glenn! I marked the thread as solved an added reputation.