LOOKUP or IF-THEN stmt - which one?

[JessP27]

First, I apologize for the vague Subject, but I'm not sure what I need here....

I need to have a formula to look at two values and determine the result
based on the where the value falls in a range...

Column A
(A1) - 39,841
(A2) - 20,623
(A3) - 24,496
(A4) - 23,909

Column B
(B1) - 1,749
(B2) - 635
(B3) - 1,142
(B4) - 965

IF the number in A2 is between 38000 and 40000
OR
If the number in B2 is between 2000 and 2150
THEN "B1" should be the result in C2

IF the number in A2 is between 20000 and 37999
OR
If the number in B2 is between 1000 and 1999
THEN "B2" should be the result in C2

(I will need to repeat this pattern a couple more times as well)

I used =LOOKUP(B2,{0;10000;20000;38000},{"B4";"B3";"B2";"B1"}) to get a
result on the first set of criteria, but I just can't seem to figure out how
to get the formula to look at the second set of criteria as an OR.

Please let me know if you have any suggestions as soon as possible, as I'm
trying to get a report out yet this afternoon.

Thanks very much in advance,
Jessica

 

[T. Valko]

Think you'd be better off creating a lookup table:

columns F, G, H, I, J
1...38000, 40000, 2000, 2150, B1
2...20000, 37999, 1000, 1999, B2
3...10000, 19999, 500, 999, B3

Then, this array formula** entered in C1 and copied down as needed:

=INDEX(J$1:J$3,MATCH(TRUE,((A1>=F$1:F$3)*(A1<=G$1:G$3)+(B1>=H$1:H$3)*(B1<=I$1:I$3))>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[JessP27]

Biff,
I'm not sure if I missed something or not -
I copied the formula as it's posted below and the formula is showing as an
array, but something seems to be amiss...

A4 shows 40,952
B4 shows 1,846
C4 shows B2, but should be B1

A8 shows 33,245
B8 shows 1,928
C8 shows B2, but should be B1

It looks like some of the results are correct, but not all of them. Any ideas?

I'd be happy to send the spreadsheet if that would help...
Thanks again,
Jessica

 

[JessP27]

Biff,
I'm not sure if I missed something or not -
I copied the formula as it's posted below and the formula is showing as an
array, but something seems to be amiss...

A4 shows 40,952
B4 shows 1,846
C4 shows B2, but should be B1

A8 shows 33,245
B8 shows 1,928
C8 shows B2, but should be B1

It looks like some of the results are correct, but not all of them. Any ideas?

I’d be happy to send the spreadsheet if that would help…

Thanks again,
Jessica

 

[T. Valko]

A4 shows 40,952

B4 shows 1,846
C4 shows B2, but should be B1

Well, based on your stated requirements above C4 should be B2. At least,
that's how I interpreted your explanation.

A4 = 40,952 which is greater than 40,000 but B4 = 1,846 which is between
1000 and 1999 so A4 meets the *OR* condition of >>number in B between 1000
and 1999 THEN "B2" should be the result in C2.

 

[JessP27]

I'm sorry, I think I confused matters a little -

Here's the scenario:
B1 through B4 is a pricing level.
The customer order is priced based on where they fall in the chart. If the
order is 38,000 (column A data) or 2000 (column B data) it should be priced
at B1 - whichever it hits first. Therefore in the scenario below, I need it
to result in B1.

Between 38000 and 40000 OR 2000 and 2150 = B1 - whichever gets them to B1.
Between 20000 and 37999 OR 1000 and 1999 = B2
Between 10000 and 19999 OR 500 and 999 = B3
Between 5000 and 9999 OR 250 and 499 = B4

Again - thanks for your help!
Jessica

 

[T. Valko]

A4 shows 40,952
Between 38000 and 40000 OR 2000 and 2150 = B1

Ok, A4 is greater than 40,000 and B4 is less than 2000 so how can that
result be B1?

I'm not following you very well on this!

 

[JessP27]

Biff,
Your response helped me solve the issue - thanks so much!!

I simply updated the max columns in the lookup table and everything appears
to be working great.

Thanks again for all of your help!
Jessica

 

[T. Valko]

Good deal! Thanks for the feedback!