# VLOOKUP & ???

#### C.R.

##### Board Regular
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 ?

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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.

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

DougT & GorD,

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

C.R.

Replies
1
Views
357
Replies
1
Views
134
Replies
9
Views
438
Replies
12
Views
171
Replies
2
Views
164

1,217,259
Messages
6,135,508
Members
449,945
Latest member
noone12344444444

### 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.

### Which adblocker are you using?

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

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