View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Glenn Glenn is offline
external usenet poster
 
Posts: 1,240
Default Please can you help me

Matt via OfficeKB.com wrote:
I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail.

I have 15 sales Categories listed in Column A. Below are just 3 of them.
Each Sales category has about 5 different sets of sales ranges (colB) and
depending on the sales value a number of rep calls (Col C) is allocated for
the year.
I am trying to calculate the number of rep calls if in my main table i have
in one column the sales category and the sale value

Sales Matrix Table

Col A Col B (sales Ranges) Col C (No of Rep Calls)
Direct Retailer £0 0
Direct Retailer £5000 5
Direct Retailer 10000 10
Direct Retailer 20000 20
Direct Retailer 50000 40
Wholesaler 0 0
Wholesaler 15000 15
Wholesaler 35000 20
Wholesaler 65000 25
Wholesaler 100000 40
Garden Centre 0 2
Garden Centre 5000 5
Garden Centre 7500 10
Garden Centre 10000 20
Garden Centre 20000 25

Now if in my main sales data table in Col A i have Wholesaler, with a sales
value of £75K in Col B, i need the result to return 25 Rep Calls in Col C
If i was to have say Garden Centre as the category with a sales value of £500,
the rep calls should return 2

I really hope this helps explain what i need and that someone can help. The
main sales data table is over 5000 rows, so if there is a solution i would be
over the moon.
Many Thanks



Array formula (commit with CTRL+SHIFT+ENTER):

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$50 00,""),Data!$C$1:$C$5000)