Home |
Search |
Today's Posts |
#1
|
|||
|
|||
(if or) function
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!!! |
#2
|
|||
|
|||
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) "cwindom1" wrote in message ... 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!!! |
#3
|
|||
|
|||
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 "cwindom1" wrote in message ... 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!!! |
#4
|
|||
|
|||
Whew!
=("900"&IF((A1=10500)*(A1<=10543)+(A1=20000)* (A1<=25493),"04","")&IF((A1=30000)*(A1<=39999),"0 5","") &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 -----Original Message----- 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!!! . |
#5
|
|||
|
|||
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.... "Bob Phillips" wrote: 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) "cwindom1" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |