Return Value Based on Criteria

[kumara_faith]

Hi,

I have the following tables in Sheet 1 and Sheet 2:

Sheet 1					Sheet 2
Month	Branch	Name	Rate		Branch	Name	Jan	Feb
Jan	Texas	Staff1	56%		Texas	Staff1	56%	36%
Jan	Virginia	Staff2	45%		Virginia	Staff2	45%	85%
Jan	Maine	Staff3	75%		Maine	Staff3	75%	92%
Feb	Texas	Staff1	36%
Feb	Virginia	Staff2	85%
Feb	Maine	Staff3	92%

In Sheet 2, I am trying to summarize the Rate for each staff based on the data from Sheet 1. I tried combining a few xlookup as below but was not successful.

=XLOOKUP(G3,C3:C8,XLOOKUP(H3,D3:D8,XLOOKUP(I2,B3:B8,E3:E8)))

Appreciate all the help.

 

[jdellasala]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Month	Branch	Name	Rate		Branch	Name	Jan	Feb
2	Jan	Texas	Staff1	56%		Texas	Staff1	56%	36%
3	Jan	Virginia	Staff2	45%		Virginia	Staff2	45%	85%
4	Jan	Maine	Staff3	75%		Maine	Staff3	75%	92%
5	Feb	Texas	Staff1	36%
6	Feb	Virginia	Staff2	85%
7	Feb	Maine	Staff3	92%
Sheet6

Cell Formulas
Range	Formula
H2:I4	H2	=SUMIFS(D2:D7,B2:B7,F2:F4,C2:C7,G2:G4,A2:A7,H1:I1)
Dynamic array formulas.

 

[Legacy 143009]

Sheet 2 B2:

=IFERROR(INDEX('Sheet 1'!$C$2:$C$7, MATCH(0, INDEX(COUNTIF($B$1:B1, 'Sheet 1'!$C$2:$C$7), 0, 0), 0)), "")

Sheet 2 A2:

=IFERROR(INDEX('Sheet 1'!$B$2:$B$7, MATCH($B2,'Sheet 1'!$C$2:$C$7,0)), "")

Sheet 2 C2:

=INDEX('Sheet 1'!$D$2:$D$7, MATCH(TRUE,('Sheet 1'!$C$2:$C$7=$B2)*('Sheet 1'!$A$2:$A$7=C$1),0))

Sheet 2 D2:

=INDEX('Sheet 1'!$D$2:$D$7, MATCH(TRUE,('Sheet 1'!$C$2:$C$7=$B2)*('Sheet 1'!$A$2:$A$7=D$1),0))

 

[kumara_faith]

Hi jdellasala and Flashbond,

Thank you for your solution and appreciate it. Have a great day ahead.