# How do I perform VLOOKUP or INDEX MATCH based on three different criteria?

I can't figure out the proper formula to lookup a value based on three different criteria. I want to fill out the summary table with data from the source table.

I tried using INDEX, MATCH and SUMPRODUCT but the value always returned "-" instead of the correct value. Not sure how to combine the formula together

Say I want to fill in the Summary Table D16 where PersonA matches for both tables, the data is for Quarter 2 and the data would match the type which is Expense. How do I do this?

Use INDEX to return the correct row:

``````INDEX(B:G,MATCH(A16,A:A,0),0)
``````

Then use SUMIFS to return the correct Column's Value in that range:

``````=SUMIFS(INDEX(B:G,MATCH(A16,A:A,0),0),\$B\$2:\$G\$2,\$C16,\$B\$3:\$G\$3,D\$15)
``````

Put the above in D16, copy over and down.

• nice! I was running through some sort of aggregate sumproduct option. Much better to avoid the array calc option. +1 – Forward Ed Jan 9 at 13:32