Index - Match

[ekslit]

Hello Guys,

Happy new year

I have a small issue with one formula and hope that you can help me with it.
I m using this formula in sheet A : =((INDEX(SheetB!$C$2:$C1000,MATCH(C2,SheetB!$A$2:$A$1000,0)))/8)*24 -->This help me to print 13:00 because i have in my Sheet A the Value 1 but not Value 2/3 and 4.
How can i get the value of C2, C3 and C4? using C1?

Sheet B preview

Thanks in advance for your help.

 

[jasonb75]

There is not enough information to answer your question?

Why 1:00 12:00 and 0:00 in those 3 cells? Why not 2:00 5:00 and 6:00, or any other combination?

 

[ekslit]

Hello @jasonb75 ,
I need to get the child of label A and i have always 3 childs thats why i m looking for 1:00 / 12:00 and 0:00 and not other combination.
Hope that its more clear now

 

[jasonb75]

Not at all, you say that you only have a source for the starting 13:00 and none of the others so there is absolutely no logic to work with.
What about different times in C1? We're gonna need more than 1 example to have any hope of figuring something out.

 

[ekslit]

Please find below example for Sheet A and Sheet B.
the G column is using the formula i gave above and idea is to double check that sum of child equal to parent (label A, label B etc...)

 

[jasonb75]

That makes it clearer, from your earlier posts it looked like you wanted to create the times in column C from those in column G.
In H2, fill down as needed.

=SUMIFS(C:C,B:B,"*child of "&F2&"*")

 

[ekslit]

Hello @jasonb75 thanks for the help. This works fine.
But i l afraid if i'm having some label text issue, this solution will not be correct.
is there any way, to use the key. For instance "Value1" and sum automatically the 3 row below this "Value 1" for label A ?

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUM(INDEX($C$2:$C$100,MATCH(E2,$A$2:$A$100,0)+{1,2,3}))

This may need array entry depending on your version of Excel.