Please can you help me

[Matt via OfficeKB.com]

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

 

[Glenn]

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$5000,""),Data!$C$1:$C$5000)

 

[Mattlynn via OfficeKB.com]

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

I see its looking up the category B1 in a tab called data in col A, and the
sales ranges in Col B, and bringing back the rep calls in Col C, but how does
it know what mysales actual was

Thanks
Matt

I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail.

[quoted text clipped - 34 lines]

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$5000,""),Data!$C$1:$C$5000)

 

[Glenn]

You said the the category would be in column A and the sales value would be in
column B. In my formula, I referenced A1 for category and B1 for sales value.

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5000,""),Data!$C$1:$C$5000)
______^^^^____________________^^^^________________________________________

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

I see its looking up the category B1 in a tab called data in col A, and the
sales ranges in Col B, and bringing back the rep calls in Col C, but how does
it know what mysales actual was

Thanks
Matt

I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail.

[quoted text clipped - 34 lines]

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$5000,""),Data!$C$1:$C$5000)

 

[Glenn]

To help clarify what is happening, look at the middle of the formula:


IF( Data!$A$1:$A$5000 = A1 , Data!$B$1:$B$5000 , "" )


If the value in column A (Category) from Data matches the Category entered in
cell A1, it returns the value from column B (Sales Range) in Data, otherwise it
returns "". Because the formula references multiple cells and was array
entered, the result is an array of values and now your formula looks something
like this:


=LOOKUP(B1,
{"", "", "", 0, 15000, 35000, 65000, 10000, "", "", ""},
Data!$C$1:$C$5000)


The LOOKUP compares the sales value entered in B1 to the array and returns the
corresponding value in column C (Rep Calls) from Data, following the rules of
the LOOKUP function.

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

I see its looking up the category B1 in a tab called data in col A, and the
sales ranges in Col B, and bringing back the rep calls in Col C, but how does
it know what mysales actual was

Thanks
Matt

I posted this earlier but managed to confuse Jarek who kindly offered some
solutions, but to no avail.

[quoted text clipped - 34 lines]

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$5000,""),Data!$C$1:$C$5000)

 

[Mattlynn via OfficeKB.com]

Hi Glenn
Thanks for all this.
I just cant get it to work out, may i send you the spreadsheet for you to
have a look for me.
Many Thanks
Matt

You said the the category would be in column A and the sales value would be in
column B. In my formula, I referenced A1 for category and B1 for sales value.

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5000,""),Data!$C$1:$C$5000)
______^^^^____________________^^^^________________________________________

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

[quoted text clipped - 14 lines]

 

[Mattlynn via OfficeKB.com]

Glenn
You're a star - with some slight changing to the referenced cells, i managed
to get this to work - you're a bloody clever person

Many thanks
Matt

You said the the category would be in column A and the sales value would be in
column B. In my formula, I referenced A1 for category and B1 for sales value.

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5000,""),Data!$C$1:$C$5000)
______^^^^____________________^^^^________________________________________

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

[quoted text clipped - 14 lines]

 

[Glenn]

Glad you got it working.

Glenn
You're a star - with some slight changing to the referenced cells, i managed
to get this to work - you're a bloody clever person

Many thanks
Matt

You said the the category would be in column A and the sales value would be in
column B. In my formula, I referenced A1 for category and B1 for sales value.

=LOOKUP(B1,IF(Data!$A$1:$A$5000=A1,Data!$B$1:$B$5000,""),Data!$C$1:$C$5000)
______^^^^____________________^^^^________________________________________

Thanks Glenn, but where does this formaul look at the actual sales value
reached by the customer.

[quoted text clipped - 14 lines]

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