Multiple IF-INDEX-MATCH formula

[manpritspa]

Hi all,

I have a workbook with 5 worksheets. One "Summary" sheet, and others are "K-08", "K-09", "Narrow" and "Master Date".
Summary sheet is where you plan production for three machines-K08, K-09 and Narrow. You need to give priority for each machine with Job name and expected quantity. Then sheets K-08, K-09 and Narrow will derive date from Summary sheet and Master sheet and calculate Expected Delivery date.

Summary Sheet Format is as follows

C D E F G

2 Job Name Quantity Machine Priority Delivery Date

3 Job A 2000 K-08 1 #Value!

4 Job B 5000 K-09 1 #Value!

5 Job C 250 Narrow 1 #Value!


Now at summary sheet, in cell G2, I want delivery date to be displayed where it matches machine, job name, and priority.

I am trying this but not working:

=IF(E3="K-08",(INDEX('K-08'!$R$3:$R$52,MATCH('Delivery Date Planner'!C3&'Delivery Date Planner'!F3,'K-08'!$D$3:$D$52&'K-08'!$C$3:$C$52,1)))),IF(E3="K-09",(INDEX('K-09'!$R$3:$R$52,MATCH('Delivery Date Planner'!C3&'Delivery Date Planner'!F3,'K-09'!$D$3:$D$52&'K-09'!$C$3:$C$52,1)))),IF(E3="Narrow",(INDEX(Narrow!$R$3:$R$52,MATCH('Delivery Date Planner'!C3&'Delivery Date Planner'!F3,Narrow!$D$3:$D$52&Narrow!$C$3:$C$52,1))))


Please help!


Manprit

 

[BarryL]

can you try

=INDEX(INDIRECT("'"&E3&"'!$R$3:$R$52"),MATCH('Delivery Date Planner'!C3&'Delivery Date Planner'!F3,'K-08'!$D$3:$D$52&INDIRECT("'"&E3&"'!$$C$3:$C$52"),1))