Index and Match for both columns and rows

**sofib09** · 12-08-2010, 01:53 PM

Hi,

I'd like some assistance in developing a formula that can use INDEX and MATCH for both columns and rows.

I've attached a file as an example. I've been able to successfully write a formula matching the Category and Subtype given the appropriate Option (Option C), but now I want to add functionality that also allows me to select the price by also using the Options drop-down menu (i.e., A,B,C...) in addition. Right now, I've got the formula set at Option C, but I'd also like to have the Option row be included in the INDEX and MATCH formula.

In other words, I've successfully implemented using INDEX and MATCH for columns, so now I'd like to know how to add in a criterion that's contained within a row.

Apologies if this is a very basic or dumb question, but I'm new to Index and Match and want to be sure I implement it properly.

The drop-down menus are highlighted green, and my formula and intended result is highlighted in yellow.

Thanks for your help!

**DonkeyOte** · 12-08-2010, 01:58 PM

If you're using XL2007 as implied it would be simplest to use SUMIFS

=SUMIFS(INDEX(D3:K20,0,MATCH(D24,D2:K2,0)),A3:A20,A24,B3:B20,B24,C3:C20,C24)

Even if you opt against SUMIFS the above illustrates the INDEX concept with MATCH to utilise the appropriate column
(your existing solution appears to be ignoring Type)

**sofib09** · 12-08-2010, 03:29 PM

That's awesome...worked perfectly!

And yes, I did ignore "Type" since I'm using that for something else...but once I removed it, the formula worked perfectly. Thanks!