Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
I have a column of dates that i what to do a lookup on. The column looks
like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
I put a FALSE in my VLOOKUP, and with your given data, I get #N/A, I put
TRUE, and I get 6. More info is needed. Is your talbe always going to be the first of every month, and you are just matching month/year? Have you verified that all dates are actual dates, and not text? -- ** John C ** "Jack" wrote: I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
If you put FALSE you should get #N/A so something "aint" right here
=VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
2/1/2008 5 2/15/2008 5 TRUE
3/1/2008 6 #N/A FALSE 4/1/2008 7 This is vlookup im using and the result - see above is 5 & #n/A - how do i get 6 =VLOOKUP($D$16,$A$16:$B$18,2,TRUE) Table will not always be the first of the month - just my example "John C" wrote: I put a FALSE in my VLOOKUP, and with your given data, I get #N/A, I put TRUE, and I get 6. More info is needed. Is your talbe always going to be the first of every month, and you are just matching month/year? Have you verified that all dates are actual dates, and not text? -- ** John C ** "Jack" wrote: I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
Your sample data here is different than your prior sample data. You had Jan,
Feb, Mar listed first, and stated you wanted the Feb data (i.e.: 6), now you have Feb, Mar, and Apr, and now state you want the Mar data. Please clarify. Also please clarify what is the criteria for a match. Using your 2nd example, 2/15 is equally distant from 3/1 and 2/1 (let's pretend it wasn't a leap year), why would I want the 2/1 data over 3/1 data, or vice versa. -- ** John C ** "Jack" wrote: 2/1/2008 5 2/15/2008 5 TRUE 3/1/2008 6 #N/A FALSE 4/1/2008 7 This is vlookup im using and the result - see above is 5 & #n/A - how do i get 6 =VLOOKUP($D$16,$A$16:$B$18,2,TRUE) Table will not always be the first of the month - just my example "John C" wrote: I put a FALSE in my VLOOKUP, and with your given data, I get #N/A, I put TRUE, and I get 6. More info is needed. Is your talbe always going to be the first of every month, and you are just matching month/year? Have you verified that all dates are actual dates, and not text? -- ** John C ** "Jack" wrote: I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
You need to have all the dates in ascending order in column 1. Maybe the
values are text, they need to be numerical for dates to work -- Regards, Peo Sjoblom "Jack" wrote in message ... 2/1/2008 5 2/15/2008 5 TRUE 3/1/2008 6 #N/A FALSE 4/1/2008 7 This is vlookup im using and the result - see above is 5 & #n/A - how do i get 6 =VLOOKUP($D$16,$A$16:$B$18,2,TRUE) Table will not always be the first of the month - just my example "John C" wrote: I put a FALSE in my VLOOKUP, and with your given data, I get #N/A, I put TRUE, and I get 6. More info is needed. Is your talbe always going to be the first of every month, and you are just matching month/year? Have you verified that all dates are actual dates, and not text? -- ** John C ** "Jack" wrote: I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
Let me backup and start over.
Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
Your VLOOKUP formula is not correct if you want to lookup the date
=IF(ISNA(VLOOKUP(D1,A1:B6,2)),"",VLOOKUP(D1,A1:B6, 2)) will return High and =IF(ISNA(VLOOKUP(D1,A1:B6,3)),"",VLOOKUP(D1,A1:B6, 3)) will return 1 -- Regards, Peo Sjoblom "Jack" wrote in message ... Let me backup and start over. Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
i want to look up the date first. which sould give me rows 1 & 2 based on
looking up 1/15/08 then i want to find the value for "Low" - which should give me the answer from row 1 "Peo Sjoblom" wrote: Your VLOOKUP formula is not correct if you want to lookup the date =IF(ISNA(VLOOKUP(D1,A1:B6,2)),"",VLOOKUP(D1,A1:B6, 2)) will return High and =IF(ISNA(VLOOKUP(D1,A1:B6,3)),"",VLOOKUP(D1,A1:B6, 3)) will return 1 -- Regards, Peo Sjoblom "Jack" wrote in message ... Let me backup and start over. Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
It should give you row 2 not 1 & 2
-- Regards, Peo Sjoblom "Jack" wrote in message ... i want to look up the date first. which sould give me rows 1 & 2 based on looking up 1/15/08 then i want to find the value for "Low" - which should give me the answer from row 1 "Peo Sjoblom" wrote: Your VLOOKUP formula is not correct if you want to lookup the date =IF(ISNA(VLOOKUP(D1,A1:B6,2)),"",VLOOKUP(D1,A1:B6, 2)) will return High and =IF(ISNA(VLOOKUP(D1,A1:B6,3)),"",VLOOKUP(D1,A1:B6, 3)) will return 1 -- Regards, Peo Sjoblom "Jack" wrote in message ... Let me backup and start over. Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
Hi,
Your sample keeps changing, the formula you are showing us =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") has nothing to do with dates because all LOOKUP is doing is telling you that whether a date in D1 is on or after the date in A1. I think this may be what you want: =SUMPRODUCT(--(A1:A6<=D1),--(E1=B1:B6),C1:C6) -- Thanks, Shane Devenshire "Jack" wrote: Let me backup and start over. Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup with Dates
I'm using the sample data to clarify what I'm trying to do. Let's try this
Apple Low 5 Banana Low 6 Apple High 1 Orange Low 6 Orange High 2 Peach Low 7 Peach High 3 I'm looking for the result that appears prior to the item being looked up. in the sample lookup for Banana the formula find the result for row 3 with Orange. I want the result prior to Orange Low, Apple Low - row 1 answer 5 I'm not trying to add the data - just find it. "ShaneDevenshire" wrote: Hi, Your sample keeps changing, the formula you are showing us =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") has nothing to do with dates because all LOOKUP is doing is telling you that whether a date in D1 is on or after the date in A1. I think this may be what you want: =SUMPRODUCT(--(A1:A6<=D1),--(E1=B1:B6),C1:C6) -- Thanks, Shane Devenshire "Jack" wrote: Let me backup and start over. Here is the spread sheet A B C D E F 1/1/2008 Low 5 1/15/2008 Low 6 1/1/2008 High 1 2/1/2008 Low 6 2/1/2008 High 2 3/1/2008 Low 7 3/1/2008 High 3 This is the formula =IF(LOOKUP(D1,A1:A6,A1:A6)0,VLOOKUP(E1,B1:C6,2,TR UE),"") I need the information from the row with the date prior to the date I'm looking for. There are different types of data for each date so the second lookup. "Peo Sjoblom" wrote: If you put FALSE you should get #N/A so something "aint" right here =VLOOKUP(D1,A1:B3,2) where D1 holds 2/15/08 and A1:B3 is your example table I get 6 as expected -- Regards, Peo Sjoblom "Jack" wrote in message ... I have a column of dates that i what to do a lookup on. The column looks like the following: 01/01/08 5 02/01/08 6 03/01/08 7 I what to find the value in column two for the date 2/15/08. If i put False in the vlookup formula i get 5 as the answer, if i put True i get 7 as the answer. How do i get the answer from the row with 2/01/08. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup on Dates | Excel Worksheet Functions | |||
vlookup using dates | Excel Discussion (Misc queries) | |||
vlookup and dates! | Excel Worksheet Functions | |||
dates and vlookup | Excel Worksheet Functions | |||
VLookUp with Dates | Excel Worksheet Functions |