Formula issue, tips?

[Camel123]

Hi,

Please see snapshot:
https://imgur.com/zrCKcUQ

I want a formula in sheet 2; if the product number from sheet 2 is in sheet 1, then retrieve all costs for that product from sheet 1 to sheet 2 per month. When 2019-05 has passed, all dates are shifted one cell to the left, meaning 2019-06-01 will appear in B2. This is due to how the system works from where I export the records in Sheet 1. Dates in sheet 2 are not changing though.

I tried some combinations with index, match, lookup, sumifs, sumif etc. but can't get it working.

 

[DanteAmor]

And if on sheet 2 in cell B2 you put the formula
=Sheet1!B2

And copies the formula to the right

 

[Marcelo Branco]

Maybe (not tested)

B3 copied across and down
=INDEX(Sheet1!$B$3:$D$100,MATCH($A3,Sheet1!$A$3:$A$100,0),MATCH(B$2,SHeet1!$B$2:$D$2,0))

M.

 

[Camel123]

And if on sheet 2 in cell B2 you put the formula
=Sheet1!B2

And copies the formula to the right

Can’t have it that way unfortunately :/. Sheet 2 need to remain fixed/absolute.

 

[Camel123]

Maybe (not tested)

B3 copied across and down
=INDEX(Sheet1!$B$3:$D$100,MATCH($A3,Sheet1!$A$3:$A$100,0),MATCH(B$2,SHeet1!$B$2:$D$2,0))

M.

Not working :/

 

[Marcelo Branco]

Not working :/

Worked perfectly for me

Sheet2

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

A​

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

B​

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

C​

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

D​

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

1​

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

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

2​

[/td][td]

Product Number​

[/td][td]

2019-05-01​

[/td][td]

2019-06-01​

[/td][td]

2019-07-01​

[/td][/tr]

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

3​

[/td][td]

771​

[/td][td]

100​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

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

4​

[/td][td]

881​

[/td][td]

0​

[/td][td]

200​

[/td][td]

200​

[/td][/tr]

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

5​

[/td][td]

993​

[/td][td]

500​

[/td][td]

500​

[/td][td]

500​

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

Formula in B3 copied across and down
=INDEX(Sheet1!$B$3:$D$100,MATCH($A3,Sheet1!$A$3:$A$100,0),MATCH(B$2,Sheet1!$B$2:$D$2,0))

M.

 

[Camel123]

I found out the problem was in the lookup array of the first match, I referred to the whole column "A:A" instead and it retrieved incorrect values and also sorting it under the incorrect product number for some reason. Then I changed it to A3-A9999 and the function works perfect, many thanks! Though I am interested in why it returns wrong values next to an incorrect product number that does not match, when referring to the whole column? Basically, it did not return values for the first rows, for the rows where values were returned those were shifted down and therefor not matching with the correct product number.

=INDEX(Sheet1!$B$3:$D$100,MATCH($A3,Sheet1!$A$3:$A$100,0),MATCH(B$2,Sheet1!$B$2:$D$2,0))