Formula for a Range of Numbers
Posted by Rick D. on October 25, 2001 2:09 PM
Help! I have two columns with numbers. Column A is the Start Range and column B is the end range. Ex: A1 has 9000200000 B1 has 9000500000 and on column C has Product type. I have another file with numbers that need to fit that range to determine the product type.
I don't know the formula to do this function.
Posted by Lars on October 25, 2001 2:25 PM
=if(and(a1>=9000200000,9000500000<=b1),"(Product type)","")
This will say if a number is in this range including the minimum and max then return product type if it's one product type
Posted by Mark W. on October 25, 2001 2:28 PM
Okay, 1st of all sort your worksheet (Sheet1) with
your 'Start Range', 'End Range', and 'Product Types'
in ascending order based on 'Start Range'.
Next, suppose that your other worksheet (Sheet2)
has its 'Range Value' in cell Sheet2!A1. Just
enter the array formula,
{=VLOOKUP(Sheet2!A1,Sheet1!A:C,{2,3})},
into cells Sheet2!B1:C1.
Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.
Next, enter the formula,
=IF(ISNA(B1),"",IF(A1<=B1,C1,"")), into
the Sheet2!D1. Finally, hide columns Sheet2!B:C.
