If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Lookup
Hi, assuming the following data:
A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#2
|
|||
|
|||
Date Lookup
Hi,
If there was a 1 jan in your data what would you want to return then, an exact match or the next highest? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#3
|
|||
|
|||
Date Lookup
Hi Mike,
If there is an exact match, i would like it to return the exact match. "Mike H" wrote: Hi, If there was a 1 jan in your data what would you want to return then, an exact match or the next highest? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#4
|
|||
|
|||
Date Lookup
Hi
Try the following Array entered formula =INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2) To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert curly braces { } around the formula. Do not type the braces yourself. {=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)} -- Regards Roger Govier ck13 wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#5
|
|||
|
|||
Date Lookup
Hi,
In that case there are 2 options. 1. If column A is sorted ascending then you can get away with =INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1) 2. If column A isn't sorted you need =INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi Mike, If there is an exact match, i would like it to return the exact match. "Mike H" wrote: Hi, If there was a 1 jan in your data what would you want to return then, an exact match or the next highest? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#6
|
|||
|
|||
Date Lookup
Hi
I forgot to add, I placed the date to be found (01 Jan 2010) in cell C1 -- Regards Roger Govier Roger Govier wrote: Hi Try the following Array entered formula =INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2) To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert curly braces { } around the formula. Do not type the braces yourself. {=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)} -- Regards Roger Govier ck13 wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#7
|
|||
|
|||
Date Lookup
Or, slightly shorter, with 01 Jan 2010 in C1
{=INDEX(A1:B3,MATCH(TRUE,MONTH(C1)=MONTH(A1:A3),0) ,2)} -- Regards Roger Govier Roger Govier wrote: Hi Try the following Array entered formula =INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3, "mmm"),0),2) To Array enter or amend, use Control+Shift+Enter (CSE) not just Enter. When you use CSE, Excel will insert curly braces { } around the formula. Do not type the braces yourself. {=INDEX(A1:B3,MATCH(TRUE,TEXT(C1,"mmm")=TEXT(A1:A3 ,"mmm"),0),2)} -- Regards Roger Govier ck13 wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#8
|
|||
|
|||
Date Lookup
Hi Mike,
Thanks you once again.. it works "Mike H" wrote: Hi, In that case there are 2 options. 1. If column A is sorted ascending then you can get away with =INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1) 2. If column A isn't sorted you need =INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi Mike, If there is an exact match, i would like it to return the exact match. "Mike H" wrote: Hi, If there was a 1 jan in your data what would you want to return then, an exact match or the next highest? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
#9
|
|||
|
|||
Date Lookup
Glad I could help and thanks for the feedback
-- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi Mike, Thanks you once again.. it works "Mike H" wrote: Hi, In that case there are 2 options. 1. If column A is sorted ascending then you can get away with =INDEX(B1:B10,COUNTIF(A1:A10,""&C1)+1) 2. If column A isn't sorted you need =INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10," "&C1)+1),A1:A10,0)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi Mike, If there is an exact match, i would like it to return the exact match. "Mike H" wrote: Hi, If there was a 1 jan in your data what would you want to return then, an exact match or the next highest? -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ck13" wrote: Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks |
Thread Tools | |
Display Modes | |
|
|