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.

	01/01/2018	06/01/2018
Project a	3	1
Total	2	2
	01/01/2018	02/01/2018	03/01/2018	04/01/2018	05/01/2018	06/01/2018		I have an input table from c2 thru e4 and I would like to fill in automatically c6:i9
Project a	0	0	0	0	0	0
Other	0	0	0	0	0	0
Total	0	0	0	0	0	0

<colgroup><col><col span="6"><col span="7"></colgroup><tbody>
</tbody>

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...

	C​	D​	E​	F​	G​	H​	I​
1​		01/01/2018​	06/01/2018​
2​	Project a​	3​	1​
3​
4​	Total​	2​	2​
5​
6​		01/01/2018​	02/01/2018​	03/01/2018​	04/01/2018​	05/01/2018​	06/01/2018​
7​	Project a​	3​	0​	0​	0​	0​	1​
8​	Other​	0​	0​	0​	0​	0​	0​
9​	Total​	2​	0​	0​	0​	0​	2​

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.