MATCH and INDEX into a matrix

Question

I did some research online regarding how to lookup values with two criteria. I found out it is done using MATCH and INDEX. But somehow I just can't make it work. I did press Ctrl+Shift+Enter. Here's my formula:

=INDEX(Tables!$F$3:$G$6,MATCH(1,(D2=Tables!$E$3:$E$6)*(H2=Tables!$F$2:$G$2),0))

Example scenario is that I have a column called Entitled Discount. I have Gold, Silver and Bronze Member. Gold has 5% if buying Product but has 3% if buying Package. Silver has 10% if buying Product but has 4% when buying Package:

 Membership     Package   Product 
   None            0%        0%
   Gold            3%        5%
   Silver          4%       10%
   Bronze          5%       15%

Since your formula is incorrect we need more info. You are trying to return the correct percentage? Show an image of the worksheet, please. — Excel Hero, Oct 30 '15 at 16:55

score 2 · Accepted Answer · answered Oct 30 '15 at 17:04

2

My hunch is your data is laid out like so:

For which a formula such as:

=INDEX(E2:G6,MATCH(D2,E2:E6,0),MATCH(H2,E2:G2,0))

may suit.

answered Oct 30 '15 at 17:04

pnuts

58,317
11
87
139

1

Thank you so much it finally worked !! And such a coincidence we are using the same cells too haha :) – X-men Oct 31 '15 at 02:18

MATCH and INDEX into a matrix

1 Answers1