Lookup values with two multiple criterias

[Kamran01]

Dear Experts,

I have a data sheet, as i attached a link of image below.

C3:J3 has dates from 19/2 till 27/2.

Cell Address	Code	Consumption Range	Expected Purchase Range
A4	01040001	C8:J8	C5:J5
A12	03010003	C16:J16	C13:J13
A20	11030009	C24:J24	C21:J21

<tbody>
</tbody>

I want to calculate the sum of item based on Code & specfic date in Cosumption and Expected Purcahse.

I tried below code and got perfect result, but it is too long to remeber. Please suggest shortest code to calcuate.

=IF(A3="","",IF(A3=MRP!$A$4,INDEX(MRP!$C$8:$J$8,MATCH('Final Summary'!$F$1,MRP!$C$3:$J$3,0)),IF(A3=MRP!$A$12,INDEX(MRP!$C$16:$J$16,MATCH('Final Summary'!$F$1,MRP!$C$3:$J$3,0)),IF(A3=MRP!$A$20,INDEX(MRP!$C$24:$J$24,MATCH('Final Summary'!$F$1,MRP!$C$3:$J$3,0))))))

 

[Special-K99]

Instead of IF(INDEX(MATCH)),IF(INDEX(MATCH,IF(INDEX(MATCH)
just take the IFs out and place them in the INDEX()
so it's INDEX(IF(IF(,)),MATCH)

like this

=IF(A3="","",INDEX(IF(A3=MRP!$A$4,MRP!$C$8:$J$8,IF(A3=MRP!$A$12,MRP!$C$16:$J$16,MRP!$C$24:$J$24)),MATCH('Final Summary'!$F$1,MRP!$C$3:$J$3,0)))

 

[Kamran01]

Well done, Formula working correctly.
Can you please describe the functions seperately.

Thanks,
Kamran Noor

 

[Special-K99]

It's exactly the same formula but in a different order making it shorter.

You have
IF(A3=MRP!A4 then INDEX(MRP!C8:J8 MATCH Final Summary!F1,MRP!C3:J3,0)
IF(A3=MRP!A12 then INDEX(MRP!C16:J16 MATCH Final Summary!F1,MRP!C3:J3,0)
IF(A3=MRP!A16 then INDEX(MRP!C24:J24 MATCH Final Summary!F1,MRP!C3:J3,0)

The MATCH is the same regardless of what range is looked at
So the IF conditions can be put in the INDEX

So I've just changed it to

INDEX(
IF(A3=MRP!A4 then MRP!C8:J8
IF(A3=MRP!A12 then MRP!C16:J16
MRP!C24:J24))
,MATCH(Final Summary!F1,MRP!C3:J3,0)))

 

[Kamran01]

Dear Special - K99,

Thank you for your perfect description, can you please confirm do we have another formula to lookup this value via sumproduct, Offset, sumifs, vlookup & any other functions.

 

[Special-K99]

You asked for a shorter formula.
I provided a shorter formula.

I don't know of any others that would do it.

 

[Kamran01]

Thank you sir,