Hi,

I have data in 3 Columns

Sales Group (Col A) Sales Value (Col B) TM Calls (Col C)

Retail Direct 4999
Retail Direct 9999
Retail Direct 24999 6
Retail Direct 99999 10
Retail Direct 100000 20
Distributor 49999 15
Distrbutor 99999 25

I have another Table that shows Sales Group and Sales Value and i want to be
able to say that if you find the data in column A and Col A to bring back Col
C

I am tjhinking it should start something like =lookup(and(........but cant
work it out

Many Thanks
Matt

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

Another way of saying this is...

In Column D, i would want to say that if Column A = Retail Direct, and Column
B = 25500 sales value. then the answer should be 6
Or
If Column A =Distributor, and Column B = 200,000 then the result should be 25

Hoping someone can help !!
Thanks
Matt



Mattlynn wrote:
Hi,

I have data in 3 Columns

Sales Group (Col A) Sales Value (Col B) TM Calls (Col C)

Retail Direct 4999
Retail Direct 9999
Retail Direct 24999 6
Retail Direct 99999 10
Retail Direct 100000 20
Distributor 49999 15
Distrbutor 99999 25

I have another Table that shows Sales Group and Sales Value and i want to be
able to say that if you find the data in column A and Col A to bring back Col
C

I am tjhinking it should start something like =lookup(and(........but cant
work it out

Many Thanks
Matt


--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

Can anyone tell me if this is actually possible. I may have to completely
rethink the whole thing if its not you seem, and the deadline is nearly here
for me.

Many Many Thanks
Matt



Mattlynn wrote:
Hi,

I have data in 3 Columns

Sales Group (Col A) Sales Value (Col B) TM Calls (Col C)

Retail Direct 4999
Retail Direct 9999
Retail Direct 24999 6
Retail Direct 99999 10
Retail Direct 100000 20
Distributor 49999 15
Distrbutor 99999 25

I have another Table that shows Sales Group and Sales Value and i want to be
able to say that if you find the data in column A and Col A to bring back Col
C

I am tjhinking it should start something like =lookup(and(........but cant
work it out

Many Thanks
Matt


--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

your explanations are a bit vague
in no row of yr data are sales value 25500 when A=Retail Direct
same with A =Distributor, and Column B = 200,000

you surely are not looking for sth. like:
=IF(AND(A1="Retail Direct",B1=25500),6,IF(AND
(A1="Distributor",B1=200000),25))
?
On 5 Lis, 12:01, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
Another way of saying this is...

In Column D, i would want to say that if Column A = Retail Direct, and Column
B = 25500 sales value. then the answer should be 6
Or
If Column A =Distributor, and Column B = 200,000 then the result should be 25

Hoping someone can help !!
Thanks
Matt





Mattlynn wrote:
Hi,

I have data in 3 Columns

Sales Group (Col A) Sales Value (Col B) Â* Â* TM Calls (Col C)

Retail Direct Â* Â* Â* 4999 Â* Â*
Retail Direct Â* Â* Â* 9999 Â* Â*
Retail Direct Â* Â* Â* 24999 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*6
Retail Direct Â* Â* Â* 99999 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* 10
Retail Direct Â* Â* Â* 100000 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* 20
Distributor Â* Â* Â* Â* Â* Â* Â* Â* Â*49999 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* 15
Distrbutor Â* Â* Â* Â* Â* Â* Â* Â* Â* 99999 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* 25

I have another Table that shows Sales Group and Sales Value and i want to be
able to say that if you find the data in column A and Col A to bring back Col
C

I am tjhinking it should start something like =lookup(and(........but cant
work it out

Many Thanks
Matt

--
Matt Lynn

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Hi Jarek
Thanks for replying - will try and make less vague
The sales data are ranges ....could be £5 up to £1M.
Against each rang of sales are different categories - Retail Direct,
Distributor, Trade, Wholesaler etc, and each category has a range of sales
values that determine the number of time a rep calls to that customer.

So on the main table, if say Wholesaler has a sales value of £5000, then i
need it to firstly look in the formula table to find WHolesaler, then
determine the sales value range and return the call value.

Say i had category in Col A, sales value in Col B and calls in Col C then I
know that in Col D i could put =lookup(D2,B:C) would return the calls value,
but i am stuck as to how to say to make sure the category would also match.

Does this make more sense ?
thanks
Matt



Jarek Kujawa wrote:
your explanations are a bit vague
in no row of yr data are sales value 25500 when A=Retail Direct
same with A =Distributor, and Column B = 200,000

you surely are not looking for sth. like:
=IF(AND(A1="Retail Direct",B1=25500),6,IF(AND
(A1="Distributor",B1=200000),25))
?
Another way of saying this is...

[quoted text clipped - 37 lines]

- Pokaż cytowany tekst -

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

yes, but providing a table of ranges etc. would make it even clearer
;-)

On 5 Lis, 15:08, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
Hi Jarek
Thanks for replying - will try and make less vague
The sales data are ranges ....could be £5 up to £1M.
Against each rang of sales are different categories - Retail Direct,
Distributor, Trade, Wholesaler etc, and each category has a range of sales
values that determine the number of time a rep calls to that customer.

So on the main table, if say Wholesaler has a sales value of £5000, then i
need it to firstly look in the formula table to find WHolesaler, then
determine the sales value range and return the call value.

Say i had category in Col A, sales value in Col B and calls in Col C then I
know that in Col D i could put =lookup(D2,B:C) would return the calls value,
but i am stuck as to how to say to make sure the category would also match.

Does this make more sense ?
thanks
Matt

Jarek Kujawa wrote:
your explanations are a bit vague
in no row of yr data are sales value 25500 when A=Retail Direct
same with A =Distributor, and Column B = 200,000

you surely are not looking for sth. like:
=IF(AND(A1="Retail Direct",B1=25500),6,IF(AND
(A1="Distributor",B1=200000),25))
?
Another way of saying this is...

[quoted text clipped - 37 lines]

- Pokaż cytowany tekst -

--
Matt Lynn

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

sorry, you seem to have delivered sufficient info
will get back to you later

On 5 Lis, 15:08, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
Hi Jarek
Thanks for replying - will try and make less vague
The sales data are ranges ....could be £5 up to £1M.
Against each rang of sales are different categories - Retail Direct,
Distributor, Trade, Wholesaler etc, and each category has a range of sales
values that determine the number of time a rep calls to that customer.

So on the main table, if say Wholesaler has a sales value of £5000, then i
need it to firstly look in the formula table to find WHolesaler, then
determine the sales value range and return the call value.

Say i had category in Col A, sales value in Col B and calls in Col C then I
know that in Col D i could put =lookup(D2,B:C) would return the calls value,
but i am stuck as to how to say to make sure the category would also match.

Does this make more sense ?
thanks
Matt

Jarek Kujawa wrote:
your explanations are a bit vague
in no row of yr data are sales value 25500 when A=Retail Direct
same with A =Distributor, and Column B = 200,000

you surely are not looking for sth. like:
=IF(AND(A1="Retail Direct",B1=25500),6,IF(AND
(A1="Distributor",B1=200000),25))
?
Another way of saying this is...

[quoted text clipped - 37 lines]

- Pokaż cytowany tekst -

--
Matt Lynn

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

Ok, here goes
So if in another table i have data telling me that the customer is a retail
direct in one column, and the customer sales were 25625, then the answer
should be 10 TM calls.
If a different customer in the same category had sales of 11250, then the TM
calls should be 6.
If a customer in same category had sales of 4500, the calls would be zero.
There are actually about 15 different categories with sales ranges like this.
I can get to the TM calls result by using the Lookup, but cant link in the
sales groyp/category to make it pick the correct calls by sales value
Hope this helps to explain further Jarek, and thank you for your help
regards
Matt


Sales Group Sales Value TM Calls
Retail Direct 0
Retail Direct 5000
Retail Direct 10000 6
Retail Direct 25000 10
Retail Direct 100000 20
Retail InDirect 0
Retail InDirect 5000
Retail InDirect 10000 6
Retail InDirect 25000 10
Retail InDirect 100000 35
Indirect Retailer 0
Indirect Retailer 3000
Indirect Retailer 15000 6
Indirect Retailer 25000 15
Indirect Retailer 100000 40




Jarek Kujawa wrote:
yes, but providing a table of ranges etc. would make it even clearer
;-)

Hi Jarek
Thanks for replying - will try and make less vague
[quoted text clipped - 33 lines]

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

maybe i can email you the spreadsheet??????

Jarek Kujawa wrote:
sorry, you seem to have delivered sufficient info
will get back to you later

Hi Jarek
Thanks for replying - will try and make less vague
[quoted text clipped - 33 lines]

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

--
Matt Lynn

Message posted via http://www.officekb.com

soory for not coming back earlier but i have had my assignments
I'm still not sure whether i'm following you
here is what i've come up with:

=IF(A13="Retail Direct",OFFSET(INDIRECT("C"&MATCH("Retail Direct",$A
$1:$A$8,0)),MATCH(B13;$B$1:$B$6,1)-1,),IF(A13="Distributor",OFFSET
(INDIRECT("C"&MATCH("Distributor",$A$1:$A$8,0));MA TCH(B13,$B$7:$B
$8,1)-1,)))

with your data on actual sales volumes in A13 and lower
and your table "Sales Group Sales Value TM Calls" in A1:C8
adjust yr ranges to suit

untested

if this does not work then pls send me your workbook


On 5 Lis, 15:53, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
maybe i can email you the spreadsheet??????

Jarek Kujawa wrote:
sorry, you seem to have delivered sufficient info
will get back to you later

Hi Jarek
Thanks for replying - will try and make less vague
[quoted text clipped - 33 lines]

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1

--
Matt Lynn

Message posted viahttp://www.officekb.com

Hi jarek - Please may i have your email address.
Many Thanks
Matt



Jarek Kujawa wrote:
soory for not coming back earlier but i have had my assignments
I'm still not sure whether i'm following you
here is what i've come up with:

=IF(A13="Retail Direct",OFFSET(INDIRECT("C"&MATCH("Retail Direct",$A
$1:$A$8,0)),MATCH(B13;$B$1:$B$6,1)-1,),IF(A13="Distributor",OFFSET
(INDIRECT("C"&MATCH("Distributor",$A$1:$A$8,0));M ATCH(B13,$B$7:$B
$8,1)-1,)))

with your data on actual sales volumes in A13 and lower
and your table "Sales Group Sales Value TM Calls" in A1:C8
adjust yr ranges to suit

untested

if this does not work then pls send me your workbook

maybe i can email you the spreadsheet??????

[quoted text clipped - 11 lines]

Message posted viahttp://www.officekb.com

--
Matt Lynn

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200911/1

On 6 Lis, 09:38, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
Hi jarek - Please may i have your email address.
Many Thanks
Matt





Jarek Kujawa wrote:
soory for not coming back earlier but i have had my assignments
I'm still not sure whether i'm following you
here is what i've come up with:

=IF(A13="Retail Direct",OFFSET(INDIRECT("C"&MATCH("Retail Direct",$A
$1:$A$8,0)),MATCH(B13;$B$1:$B$6,1)-1,),IF(A13="Distributor",OFFSET
(INDIRECT("C"&MATCH("Distributor",$A$1:$A$8,0));M ATCH(B13,$B$7:$B
$8,1)-1,)))

with your data on actual sales volumes in A13 and lower
and your table "Sales Group Â* Â* Sales Value Â* Â* TM Calls" in A1:C8
adjust yr ranges to suit

untested

if this does not work then pls send me your workbook

maybe i can email you the spreadsheet??????

[quoted text clipped - 11 lines]

Message posted viahttp://www.officekb.com

--
Matt Lynn

Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200911/1- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

All sorted - {=LOOKUP(I2,IF($A$1:$A$5000=H2,$B$1:$B$5000,""),$C $1:$C$5000)}

Thanks


Jarek Kujawa wrote:


Hi jarek - Please may i have your email address.
Many Thanks
[quoted text clipped - 29 lines]

- Pokaż cytowany tekst -

--
Matt Lynn

Message posted via http://www.officekb.com

excellent

On 6 Lis, 14:55, "Mattlynn via OfficeKB.com" <u44078@uwe wrote:
All sorted - {=LOOKUP(I2,IF($A$1:$A$5000=H2,$B$1:$B$5000,""),$C $1:$C$5000)}

Thanks

Jarek Kujawa wrote:


Hi jarek - Please may i have your email address.
Many Thanks
[quoted text clipped - 29 lines]

- Pokaż cytowany tekst -

--
Matt Lynn

Message posted viahttp://www.officekb.com