(if or) function

C

cwindom1

I'm stuck....

I've got a set of data that contains a range of numbers....I'm trying to
come up with a formula that will look at the number, and depending on the
criteria based below in my data, return the correct value...the problem is I
can't figure out how to write my IF OR function formulas to accomplish this...

I need a formula that will look at the value in B2, and return the correct
value based upon this data below.....

Planner ID's Com. Code
90004 10500 through 10543 and 20000 through 25943
90005 30000 through 39999
90006 40000 through 49999
90007 50000 through 50049 and 50900 through 50999
90008 60000 through 69999
90009 75000
90010 9510 or 9511 or 9512
90011 50050 and 50500 through 50543

so for this, I need the formula to look in B2, and based upon the set of #'s
in the Com. Code column, return the corresponding value in the Planner ID
column....

this is tricky for me because I can't figure out the right combo of IF, OR,
or AND functions to get this right...any help will be greatly appreciated!!!
 
B

Bob Phillips

Do you mean

=INDEX($H:$H,MATCH(B2,$I:$I,0))

where H is the Com column, I is the Planner Id column

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

JulieD

Hi

this would be much easier if we could reorder the lookup table to the
following?
........A...............B
1.ComCode ......PlannerID
2...9510 ..........90010
3....9511 ..........90010
4....9512 ..........90010
5....9513 .............NA
6...10500 ..........90004
7...10544 .............NA
8...20000 ..........90004
9...25944 .............NA
10.30000 ..........90005
11...40000 ..........90006
12...50000 ..........90007
13...50050 ..........90011
14...50051 .............NA
15...50500 ..........90011
16...50544 .............NA
17...50900 ..........90007
18...51000 .............NA
19...60000 ..........90008
20...70000 .............NA
21...75000 ..........90009

if this is possible then you could use the VLOOKUP formula to determine the
Planner ID
e.g.
=VLOOKUP(D1,A1:B21,2)
would look up a value in D1 in the above table and return the planner's id.

hope this helps
Cheers
JulieD
 
J

Jason Morin

Whew!

=("900"&IF((A1>=10500)*(A1<=10543)+(A1>=20000)*
(A1<=25493),"04","")&IF((A1>=30000)*(A1<=39999),"05","")
&IF((A1>=40000)*(A1<=49999),"06","")&IF((A1>=50000)*
(A1<=50049)+(A1>=50900)*(A1<=50999),"07","")&IF
((A1>=60000)*(A1<=69999),"08","")&IF(A1=75000,"09","")&IF
(OR(A1={9510,9511,9512}),"10","")&IF((A1=50050)+
(A1>=50500)*(A1<=50543),"11",""))*1

If the Com. Code given isn't listed, the Planner ID will
just show "900".

HTH
Jason
Atlanta, GA
 
C

cwindom1

no, I think I might have confused my question....I've got a set of #'s in
column B, that are range of numbers, such as below....


10005
10005
10541
10541
10541
10541
20000
20000
20000
20000
20000
20000
20000
20000
20000
20000
20000


so I need the formula to look at the value in column B, and based on the
criteria under my Com. Code column, return the approriate value in Prod.
Planner column....
 
Top