Chossing right price..

[smisar]

I have MOQ and Price dadabase as above.
I want excel to choose right price for MOQ entered in quantity column.

1. if MOQ>=6000 but <21000, price 0.26
2. if MOQ>=21000 but <48000, price 0.24
3. if MOQ>=48000, price 0.21

Please suggest suitable formula...
Please help me...
regards
Sanjay Misar

 

[Khalid NGO]

HI!

Try this:
=LOOKUP(B2,{0,6000,21001,48001},{"",0.26,0.24,0.21})

Where B2 is your MOQ

Regards,

 

[smisar]

HI!

Try this:
=LOOKUP(B2,{0,6000,21001,48001},{"",0.26,0.24,0.21})

Where B2 is your MOQ

Regards,

This not correct I feel. I have more than 700 such line items.
I need a unique formula to copy to all quantity cells to get right price for quantity mentioned in B2...

Please help me...

 

[Khalid NGO]

Hi,

Post a sample excel file with manually expected output.

Regards,

 

[smisar]

Hi,

Post a sample excel file with manually expected output.

Regards,

 

[Khalid NGO]

Hi,

So your MOQ in C3 not B2, just change the cell reference:

=LOOKUP(C3,{0,6000,21001,48001},{"",0.26,0.24,0.21})
Enter in D3 and copy down


Question: Why you have 3 different columns for MOQ {A,B,C} and Price {A,B,C} ?

Regards,

 

[Khalid NGO]

Wait,

I guess you need this:
=IFERROR(INDEX(F3:M3,,MATCH(C3,F3:M3,1)),"")

Regards,

 

[smisar]

Wait,

I guess you need this:
=IFERROR(INDEX(F3:M3,,MATCH(C3,F3:M3,1)),"")

Regards,

Thank you for your help...
There is some problem in the formula... If you apply this to rows 375, 376 377 then the results are not correct. Please check the same and suggest correction.

 

[bosco_yip]

Or try this,

In D3, copy down :

=IF(C3="","",IFERROR(LOOKUP(C3,N(OFFSET(C3,,{3,6,9})),N(OFFSET(C3,,{4,7,10}))),""))

Regards
Bosco

 

[smisar]

Or try this,

In D3, copy down :

=IF(C3="","",IFERROR(LOOKUP(C3,N(OFFSET(C3,,{3,6,9})),N(OFFSET(C3,,{4,7,10}))),""))

Regards
Bosco

Thanks Bosco... It works perfectly...
If in any problem, will get back to you...
best regards
Sanjay...

 

[Khalid NGO]

Hi Sanjay,

What would be your expected answer if Quantity is less than all three MOQ? Say 5000 in C3 ?

If you are expecting Price C to be returned, try:

=IF(C3="","",IF(AND(C3>=F3,C3<I3),G3,IF(AND(C3>=I3,C3<L3),J3,IF(C3<F3,G3,M3))))

This is not recommended if you want Blank, then go with Bosco's solution.

Regards,

 

[smisar]

Hi Sanjay,

What would be your expected answer if Quantity is less than all three MOQ? Say 5000 in C3 ?

If you are expecting Price C to be returned, try:

=IF(C3="","",IF(AND(C3>=F3,C3<I3),G3,IF(AND(C3>=I3,C3<L3),J3,IF(C3<F3,G3,M3))))

This is not recommended if you want Blank, then go with Bosco's solution.

Regards,

Thanks Khalid...
I think Bosco's solution is working for me...
regards...
Sanjay