IFERROR INDEX MATCH combi formule does not work

[maarten74]

Dear Sir, Madam,

I have the following issue. I have inputfile with only data on the 1st of january and the sixth of january, but I have to fill this in on a full calendar list. I tried to solve this by using an IFERROR , INDEX and Match function, but as you can see in the attachment it only ends up with 0. Could one of you please let me know what I do wrong? Thanks in advance.


[TABLE="width: 968"]
<colgroup><col><col span="6"><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project a[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]03/01/2018[/TD]
[TD="align: right"]04/01/2018[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD="align: right"]06/01/2018[/TD]
[TD][/TD]
[TD="colspan: 6"]I have an input table from c2 thru e4 and I would like to fill in automatically c6:i9[/TD]
[/TR]
[TR]
[TD]Project a[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula used in cell d7 (the bold highlighted 0) is as follows =IFERROR(INDEX($C$2:$E$4;MATCH($C$7;$C$2:$E$4;0);MATCH(D6;$C$2:$E$4;0));0)

I do not know how to add an attachment.

Thanks in advance for your help

Best regards,

Maarten

 

[Marcelo Branco]

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

01/01/2018​

[/td][td]

06/01/2018​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Project a​

[/td][td]

3​

[/td][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Total​

[/td][td]

2​

[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

01/01/2018​

[/td][td]

02/01/2018​

[/td][td]

03/01/2018​

[/td][td]

04/01/2018​

[/td][td]

05/01/2018​

[/td][td]

06/01/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Project a​

[/td][td]

3​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Other​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Total​

[/td][td]

2​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td]

2​

[/td][/tr]
[/table]

Formula in D7 copied across and down
=IFERROR(INDEX($D$2:$E$4,MATCH($C7,$C$2:$C$4,0),MATCH(D$6,$D$1:$E$1,0)),0)

Hope this helps

M.

 

[maarten74]

Dear Marcelo,

Thanks for your feedback.
However I tried to fill this in =IFERROR(INDEX($d$2:$e$4,MATCH($C7,$C$2:$C$4,0),MATCH(D$6,$D$1:$E$1,0)),0) like you said but I get the following message

What do I do wrong?

Regards,

Maarten

 

[Marcelo Branco]

Ah, it seems your Excel version uses semicolon instead of comma as argument separator

Try
D7 copied across and down
=IFERROR(INDEX($D$2:$E$4;MATCH($C7;$C$2:$C$4;0);MATCH(D$6;$D$1:$E$1;0));0)

M.

 

[maarten74]

Hi Marcelo,

Thank you very much; it worked
Good luck Friday against Belgium!

Regards,

Maarten

 

[Marcelo Branco]

Hi Maarten,

Glad it worked.
Thank you!

M.