Excel Formula Help

[Houstonking]

Hello please need help - i have 2 tables -

Name	Amount	Period1	Period2	Period3	Period4
John	$ 350.00	Q2-24	Q3-24	Q4-24

and second table

Name	Q2-24	Q3-24	Q4-24
John

How do i get the amount of 350 in relevant period buckets

 

[ranman256]

your data should be stored like:

Name	Amount	Period
John	$350.00​	Q2-24
John	$200.00​	Q3-24
John	$100.00​	Q4-24

then you can make a crosstab to get your result.

 

[KRice]

I agree with the suggestion made by @ranman256 ...there are better ways to organize your source table that would make this task much easier. If you cannot do that, here is one option that uses a formula-based approach:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Amount	Period1	Period2	Period3	Period4		Name	Q2-24	Q3-24	Q4-24	Q1-25	Q2-25	Q3-25
2	John	350	Q2-24	Q3-24	Q4-24			John	350	350	350	0	0	0
3	Mary	400	Q3-24	Q4-24	Q1-25	Q2-25		Sue	450	450	450	0	0	0
4	John	200	Q2-24	Q3-24	Q4-24			Marc	0	225	225	225	0	0
5	Sue	450	Q2-24	Q3-24	Q4-24
6	Marc	225	Q1-25	Q3-24	Q4-24
Sheet1

Cell Formulas
Range	Formula
I2:N4	I2	=INDEX($B$2:$B$6*MMULT(--($C$2:$F$6=I$1),TRANSPOSE(COLUMN($C$1:$F$1)^0)),MATCH($H2,$A$2:$A$6,0))

Please update your profile to show which version of Excel you are using. Note that using the MATCH function like this will only consider the first row where the names match. I don't know if that is okay, or if you might have instances where multiple rows match the name.