Excel V lookup and match?

[FGaxha]

Hi masters,
I have a list of sales by region, sales person and month Jan, Feb, Mar.
I need a formula that when i search region North to bring up any sales person and monthly sales as per table. Not summary.

 

[ranman256]

a pivot table should be able to do this.

 

[AhoyNC]

Another option would be the FILTER function. Something like the example below.

Book1
	A	B	C	D	E	F	G	H	I
1	Region	Sales Person	Sales	Month		Region:	North
2	North	Per1	$ 461	Jan			Per1	$ 461	Jan
3	North	Per2	$ 542	Jan			Per1	$ 431	Mar
4	South	Per3	$ 340	Feb			Per1	$ 357	Mar
5	North	Per2	$ 305	Feb			Per2	$ 542	Jan
6	North	Per1	$ 431	Mar			Per2	$ 305	Feb
7	North	Per1	$ 357	Mar
8	South	Per3	$ 729	Mar
Sheet1

Cell Formulas
Range	Formula
G2:I6	G2	=SORT(FILTER($B$2:$D$8,$A$2:$A$8=$G$1,"No Records"))
Dynamic array formulas.

 

[FGaxha]

Another option would be the FILTER function. Something like the example below.

Book1
	A	B	C	D	E	F	G	H	I
1	Region	Sales Person	Sales	Month		Region:	North
2	North	Per1	$ 461	Jan			Per1	$ 461	Jan
3	North	Per2	$ 542	Jan			Per1	$ 431	Mar
4	South	Per3	$ 340	Feb			Per1	$ 357	Mar
5	North	Per2	$ 305	Feb			Per2	$ 542	Jan
6	North	Per1	$ 431	Mar			Per2	$ 305	Feb
7	North	Per1	$ 357	Mar
8	South	Per3	$ 729	Mar
Sheet1

Cell Formulas
Range	Formula
G2:I6	G2	=SORT(FILTER($B$2:$D$8,$A$2:$A$8=$G$1,"No Records"))
Dynamic array formulas.

this is great, exactly what i was looking for,.
I have not try it yet

 

[AhoyNC]

You're welcome. Thanks for the feedback.

 

[Peter_SSs]

this is great, exactly what i was looking for,.

In that case I have changed the marked solution from post 4 to post 3. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[FGaxha]

Another option would be the FILTER function. Something like the example below.

Book1
	A	B	C	D	E	F	G	H	I
1	Region	Sales Person	Sales	Month		Region:	North
2	North	Per1	$ 461	Jan			Per1	$ 461	Jan
3	North	Per2	$ 542	Jan			Per1	$ 431	Mar
4	South	Per3	$ 340	Feb			Per1	$ 357	Mar
5	North	Per2	$ 305	Feb			Per2	$ 542	Jan
6	North	Per1	$ 431	Mar			Per2	$ 305	Feb
7	North	Per1	$ 357	Mar
8	South	Per3	$ 729	Mar
Sheet1

Cell Formulas
Range	Formula
G2:I6	G2	=SORT(FILTER($B$2:$D$8,$A$2:$A$8=$G$1,"No Records"))
Dynamic array formulas.

My excel 2016 does no support Sort(Filter. Please any other formula similar to this?

 

[AhoyNC]

You may want to change your profile to show you are using Excel 2016 and not 365.
Formulas become a little more complicated with Excel 2016.

In the example below. Drag the formula in cell F3 down rows as needed. Formula in G3 drag down and across as needed.

Book1
	A	B	C	D	E	F	G	H	I
1	Region	Sales Person	Sales	Month		Region:	North
2	North	Per1	$ 461	Jan		Ref#	Sales Person	Sales	Month
3	North	Per2	$ 542	Jan		1	Per1	$ 461	Jan
4	South	Per3	$ 340	Feb		2	Per2	$ 542	Jan
5	North	Per2	$ 305	Feb		4	Per2	$ 305	Feb
6	North	Per1	$ 431	Mar		5	Per1	$ 431	Mar
7	North	Per1	$ 357	Mar		6	Per1	$ 357	Mar
8	South	Per3	$ 729	Mar
9
Sheet3

Cell Formulas
Range	Formula
F3:F8	F3	=IFERROR(AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($A$2:$A$8=$G$1),ROWS($M$2:M2)),"")
G3:I8	G3	=IF($F3="","",INDEX($B$2:$D$8,$F3,COLUMNS($B$1:B1)))

 

[FGaxha]

great, it works perfect. Thank you

 

[AhoyNC]

You're welcome.