vlookup with Dates

[Jack]

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.

 

[John C]

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?

 

[Peo Sjoblom]

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]

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]

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.

 

[Peo Sjoblom]

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]

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,TRUE),"")

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]

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]

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]

It should give you row 2 not 1 & 2

--


Regards,


Peo Sjoblom

 

[ShaneDevenshire]

Hi,

Your sample keeps changing, the formula you are showing us
=IF(LOOKUP(D1,A1:A6,A1:A6)>0,VLOOKUP(E1,B1:C6,2,TRUE),"") 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)

 

[Jack]

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.