Prev: Best Method
Next: How well does MS Office 2003 run on Windows Server 2008, IN PRACTICE ?
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
 | 
Pages: 1
Prev: Best Method
Next: How well does MS Office 2003 run on Windows Server 2008, IN PRACTICE ?