

New Member

April 25, 2017

I have data like this:
Month Salesman Region Product Customers Net Sales Profit / Loss
Jan-07 Joseph North FastCar 8 1,592 563
Jan-07 Joseph North RapidZoo 8 1,088 397
Jan-07 Joseph West SuperGlue 8 1,680 753
Jan-07 Joseph West FastCar 9 2,133 923
Jan-07 Joseph West RapidZoo 10 1,610 579
Jan-07 Joseph Middle SuperGlue 10 1,540 570
Jan-07 Joseph Middle FastCar 7 1,316 428
Jan-07 Joseph Middle RapidZoo 7 1,799 709
Jan-07 Lawrence North SuperGlue 8 1,624 621
Jan-07 Lawrence North FastCar 6 726 236
Jan-07 Lawrence North RapidZoo 9 2,277 966
Jan-07 Lawrence West SuperGlue 6 714 221
Question:
Need to find total sales in Jan-08,Feb-08,March-08 for salesman=Lawrence and Region=West using VLOOKUP,INDEX&MATCH functiions??
Already solved using SUMIFS,SUMPRODUCT,SUM Functions,what i need is i am stuck in using INDEX&MATCH,VLOOKUP.
Just check my formula to make changes:
Here columns B=Month C=Salesman,D=Region E=Product
=INDEX($G$4:$G$1082,MATCH(U4&W4&{“Jan-08″,”Feb-08″,”Mar-08”},($C$4:$C$1082)&($D$4:$D$1082)&($B$4:$B$1082),0))
=VLOOKUP(U4&W4&{“Jan-08″,”Feb-08″,”Mar-08”},CHOOSE({1,2},$C$4:$C$1082&$D$4:$D$1082&$B$4:$B$1082,$G$4:$G$1082),2,0)


July 16, 2010

Hi Prasanthsk,
Why are you still insisting on using INDEX & MATCH for this task? SUMIFS/SUMPRODUCT is the correct approach. INDEX & MATCH or VLOOKUP return one value, you want the sum of multiple values so you need to use SUMIFS or SUMPRODUCT....or a PivotTable.
Is this for a homework assignment, or is there a business reason for wanting to use a lookup style formula?
Mynda
Most Users Ever Online: 57
Currently Online: azri adrin, Mark Lund, Rudi
27 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Joy Berryhill
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas