Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check value against multiple ranges
I have a date and want to know in what period this is.
Reference ranges: period begin end jan 1/1/2008 14/1/2008 feb 15/1/2008 14/2/2008 mrt 15/2/2008 14/3/2008 apr 15/3/2008 14/4/2008 test value: result: 12/3/2008 mrt 1/4/2008 apr I can do this with an nested IF function, but then I´m limited to 6 nested IF statements. And I need more, in this case 7 or 8. Is there some case statement like worksheet function ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check value against multiple ranges
Hi,
Somewhere out of the way build a table that looks like this. Mine is in E1 - F4 01/01/2008 Jan 15/01/2008 Feb 15/02/2008 Mar 15/03/2008 Apr I've only done a part table because I don't understand the logic of it but you would need to build a full one and the left column must be sorted. With your date in A1 use this formula =VLOOKUP(A1,E1:F4,2,TRUE) Mike "X-Ray" wrote: I have a date and want to know in what period this is. Reference ranges: period begin end jan 1/1/2008 14/1/2008 feb 15/1/2008 14/2/2008 mrt 15/2/2008 14/3/2008 apr 15/3/2008 14/4/2008 test value: result: 12/3/2008 mrt 1/4/2008 apr I can do this with an nested IF function, but then I´m limited to 6 nested IF statements. And I need more, in this case 7 or 8. Is there some case statement like worksheet function ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check value against multiple ranges
Hi,
Assume the data is in range A5:C9 and 12/3/2008 and 1/4/2008 are in cells B17 and B18. In D6:D9, enter 1-4. In cell C17, enter the following formula CHOOSE(SUMPRODUCT(($B$6:$B$9<=$B17)*($C$6:$C$9=$B 17)*($D$6:$D$10)),A$6,A$7,A$8,A$9) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "X-Ray" wrote in message ... I have a date and want to know in what period this is. Reference ranges: period begin end jan 1/1/2008 14/1/2008 feb 15/1/2008 14/2/2008 mrt 15/2/2008 14/3/2008 apr 15/3/2008 14/4/2008 test value: result: 12/3/2008 mrt 1/4/2008 apr I can do this with an nested IF function, but then I´m limited to 6 nested IF statements. And I need more, in this case 7 or 8. Is there some case statement like worksheet function ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Check value against multiple ranges
=MyDate-14
Formatted as [$-409]mmm;@ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF statements looking up multiple ranges. | Excel Worksheet Functions | |||
Can I create Multiple passwords to edit multiple ranges? | Excel Discussion (Misc queries) | |||
How do i update multiple data ranges across multiple worksheets? | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) |