Lookup with AND i think

[Mattlynn via OfficeKB.com]

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

 

[Mattlynn via OfficeKB.com]

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 via OfficeKB.com]

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

 

[Jarek Kujawa]

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))
?

 

[Mattlynn via OfficeKB.com]

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

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 -

 

[Jarek Kujawa]

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

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 thenI
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

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))
?

[quoted text clipped - 37 lines]

- Pokaż cytowany tekst -

 

[Jarek Kujawa]

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
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 thenI
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

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))
?

[quoted text clipped - 37 lines]

- Pokaż cytowany tekst -

 

[Matt via OfficeKB.com]

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

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

 

[Mattlynn via OfficeKB.com]

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

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

 

[Jarek Kujawa]

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));MATCH(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??????

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

 

[Mattlynn via OfficeKB.com]

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

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));MATCH(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

 

[Jarek Kujawa]

(e-mail address removed)

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

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));MATCH(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

if this does not work then pls send me your workbook

[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 -

 

[Mattlynn via OfficeKB.com]

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

Thanks

(e-mail address removed)

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

[quoted text clipped - 29 lines]

- Pokaż cytowany tekst -

 

[Jarek Kujawa]

excellent

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

Thanks

(e-mail address removed)

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

[quoted text clipped - 29 lines]

- Pokaż cytowany tekst -