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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.