From: LeeL on 15 Mar 2010 05:36 Trying to find a formula that will compare a number between 2 columns are return the 3rd column Something onlong the lines of =If(And(Number>Col_A,Number<=Col_A),Col_c,"NotFound") Col_A Col_B Col_C 20100101 20100129 JAN 20100129 20100226 FEB 20100226 20100402 MAR so if Number='2010304' the result is 'MAR' -- Thanks & Best Regards From: Mike H on 15 Mar 2010 06:06 Hi, The example llokup value you gave doesn't match any of the criteria so I'll guess that's a typo. Try this ARRAY formula with the lookup value in D1 =INDEX(C1:C10,MATCH(1,(A1:A10<=D1)*(B1:B10>D1),0)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "LeeL" wrote: > Trying to find a formula that will compare a number between 2 columns are > return the 3rd column > > Something onlong the lines of > =If(And(Number>Col_A,Number<=Col_A),Col_c,"NotFound") > > Col_A Col_B Col_C > 20100101 20100129 JAN > 20100129 20100226 FEB > 20100226 20100402 MAR > > so if Number='2010304' the result is 'MAR' > -- > Thanks & Best Regards  |