# IF / INDEX-MATCH Combination

1. ## IF / INDEX-MATCH Combination

Hi All,

I'm hoping someone can help me figure this out. Essentially, here is what I'm trying to accomplish using a formula:
In cell N2, look for matches to M1 value in column B, then within these matches, look for a match to M2 value in column C. Then, given that a combination of these 2 criteria is found, make the following calculation : [(column D value on the right of two criteria match)/L1]*(column E value on the right of two criteria match)

Then the idea would be to drop this formula down the N column so that it adapts to changing values in the M columns, depending on a choice made in M1.TEST1.xlsx

I've attached a spreasheet example. Thanks for your help with this!  Register To Reply

2. ## Re: IF / INDEX-MATCH Combination

I *think* this is what you want?
=SUMPRODUCT(--(\$B\$2:\$B\$33=\$M\$1),--(\$C\$2:\$C\$33=M2),(\$D\$2:\$D\$33/\$L\$1*\$E\$2:\$E\$33))
copied down  Register To Reply

3. ## Re: IF / INDEX-MATCH Combination

Hi Fdibbins,

Thanks for your answer. I don't understand how this formula looks for a multiple criteria MATCH in order to know what values to use in the calculation?

Thanks!  Register To Reply

4. ## Re: IF / INDEX-MATCH Combination

I'm reading it the same as you Ford.

@Joshua,
Ford is using a well-known (that is, well-known to excel power-users) but informal trick to get the SUMPRODUCT function to do boolean conditional testing; that boolean conditional is the MATCH operation you're looking for.

This stackoverflow thread which is clarifying the need for number-forcing is actually also a clear and pretty succinct description of how the trick works.  Register To Reply

5. ## Re: IF / INDEX-MATCH Combination

Ben, thanks for the explanation Joshua, did it work for you?  Register To Reply