VLOOKUP & ???

C.R.

Board Regular
Joined
Jul 1, 2002
Messages
76
Hello,

I have a list that contains sales data that I download from SAP. It’s just like what’s posted except the actual list is much longer. The list is in numerical order as well. I can get VLOOKUP to find the Area number and rep name but I also want to get (separately) the product category 1,2,3 & Misc $ totals as well. I am stumped on what to use with VLOOKUP (MATCH?) to get these $ totals. Perhaps VLOOKUP isn’t the right function to use either. Any Ideas ?

Thanks in Advance.

C.R.
MrExcel.xls
ABCD
1Area#/NameCategory(USD)
2
301Smith
4
5Cat1144878
6Cat27042
7Cat311364
80
9Sub0
10total163284
110
12Misc368
130
14Grand0
15total163652
160
1702Jones0
180
19Cat198141
20Cat25546
21Cat35193
220
23Sub0
24total108879
250
26Misc1199
270
28Grand0
29total110078
Sheet1
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this, it should return the rep name and the Cat1,Cat2,&Cat3 values:

=INDEX(A:J,MATCH("01 Smith",A:A,0),1)
=INDEX(A:J,MATCH("01 Smith",A:A,0)+2,4)
=INDEX(A:J,MATCH("01 Smith",A:A,0)+2,5)
=INDEX(A:J,MATCH("01 Smith",A:A,0)+2,6)

It does assume that the offset rows and columns are the same for each rep.
 
Upvote 0
i'd use a pivot table.

First you need to fill in an entry in every row in the 1st column, but you can do that quickly.

Select column a down to the bottom of your data, press F5 to bring up the goto box.
Select blanks
Type = and then the up arrow key to point to the cell above

Then press ctrl and enter to fill in all the blanks
Select the entire row, then copy then paste special values, to replace all the formulas with hard data.

Then create your pivot table, using the page field as your selector
Book1
ABCDEFG
1Area # / NameCategory(USD)CategoryCat 2
201 SmithArea # / Name01 Smith
301 Smith
401 SmithSum of (USD)Total
501 SmithTotal7042
601 SmithCat 1144878
701 Smith
801 SmithCat 27042
901 Smith
1001 SmithCat 311364
1101 Smith
1201 Smith0
1301 Smith
1401 SmithSub0
1501 Smith
1601 Smithtotal163284
1701 Smith
1801 Smith0
1901 Smith
2001 SmithMisc368
2101 Smith
2201 Smith0
2301 Smith
2401 SmithGrand0
2501 Smith
2601 Smithtotal163652
2701 Smith
2801 Smith0
2901 Smith
3002 Jones0
Sheet1
 
Upvote 0
DougT & GorD,

Many thanks for your help.
I think one or the other of your solutions will work nicely... need to play around with both.

C.R.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top