Vlookup within a vlookup

[rfinnegan]

Hi All:

I have 50 salesmen and each has sales in 16 different product categories. I am trying to use vlookup to find the salesmen, then find the product, then give me the product total.

The data is arranged by salesman in column A, the product in column B, and the Sales figures in Column C. So, Rows 1-17 Show Mike's Data. 18-35 are Tom's, etc.

I need the vlookup to first find Mike in column A, then find shoes in column B (but only the shoes Mike sold), then return Mike's shoe total.

I realize I could modify the vlookup to search smaller areas (i.e. only look in rows 1-17 for Mike, 18-35 for Tom, etc) but we add new salemen and the data I get is not consistent in where it places new salesmen - sometimes they are at the top of the list, sometimes the bottom...

Here's a sample of what it looks like.

Thanks in advance.

Saleman desc amount
14 Shoes 2314.02
14 Linens 348.88
14 Children Textiles 528.7
14 CD/Media 646.06
14 Electronics 1995.19
14 Furniture 843.68
14 Mattress 107.98
14 Belt, Purse, Luggage 989.04
14 Jewelry 1206.46
14 Men Textiles 3582.98
14 Accessories 534.63
14 Misc Wares 3644.33
14 Kitchen Wares 266.49
14 Books 757.36
14 Toys 84.15
14 Women Textiles 7204.03
14 TOTAL 25053.98
16 Shoes 933.42
16 Linens 167.14
16 Children Textiles 490.09
16 Mattress 32.39
16 Electronics 1091.86
16 Furniture 828.74
16 CD/Media 523.54
16 Jewelry 207.29
16 Kitchen Wares 227.62
16 Belt, Purse, Luggage 610.06
16 Men Textiles 877.04
16 Misc Wares 2490.66
16 Books 349.04
16 Toys 51.5
16 Women Textiles 3017.63
16 Accessories 71.49
16 TOTAL 11969.51

 

[jasonb75]

Try

=SUMPRODUCT(--($A$2:$A$35=16),--($B$2:$B$35="Shoes"),$C$2:$C$35)

 

[rfinnegan]

Thanks, worked like a charm.

 

[redzlord]

Or you can just simply use a Pivot table.

1. Select the entire table and create PivotTable.
2. Drag or select "Salesman" to Row Labels.
3. Drag or select "Desc" to either Report Filter or Column Labels.
4. Drag or select "Amount" to Values.