VLookup with If conditions

**geng** · 06-15-2010, 01:10 AM

I have a file that I need to use vlookup to return certain dates, but I don't know how to combine If, vlookup, match index etc.

I don't even know if a cell formula will be able to return the data I want, or I have to create a code to do this.

My situation is:
I have a date in column A3(manually entered)
and I want this to be my criteria in returning my data(which is in a separate tab)

example:
A3 = 28-May-10

R16 should be the previous date on the data tab(which in this case is 14-May-10)

AH16 should be April 16
which is the 1 month prior date on file
(although there are 3 dates with April, it will return the oldest date)

AX16 should be March 8
which is the 2 months prior date on file
(although there are 3 dates with March, it will return the oldest date)

Attached is my sample file for a better understanding.

Thanks!

**scottwhittaker2333** · 06-15-2010, 02:06 AM

For R16 use this:
=SMALL(Data!A2:A701,COUNTIF(Data!A2:A701,"<"&A3)-1)
As for the rest I'm not sure.

**DonkeyOte** · 06-15-2010, 02:56 AM

Can you explain why AH16 should be 16-Apr rather than 23-Apr given the latter (rather than the former) is the last date on Data sheet that occurs 1 month prior to 28-May.

**geng** · 06-15-2010, 04:02 AM

actually, what I want to do there is ---
If Date(A3) is in the 4th week of the month
AH16 should be in the 2nd week of the previous month

If Date(A3) is in the 3rd week of the month
AH16 should be in the 2nd week of the previous month

If Date(A3) is in the 2nd week of the month
AH16 should be in the 1st week of the previous month

If Date(A3) is in the 1st week of the month
AH16 should be in the 1st week of the previous month

with AX16 it should be in the week of the 2 previous month(instead of just 1 month)

Thanks for your time helping me!

**KeesValkenswaar** · 06-15-2010, 05:59 AM

Have a look at the attachment. It contains a few solutions.
There are many solutions. This one is perhaps a little bit with too many additional checks. However, I want to be sure to have the info from the correct cell.
More checks could be added in case a combination is missing. The zeros can easlily be removed by options or formatting.

Just let me know if this is towards your requirements.

**geng** · 06-15-2010, 07:15 PM

Hmmmmm, I think this fits my requirements...

Thank you so much! I really appreciate it!!!

Mwahhh! Mwahhh!Mwahhh!!!

**geng** · 06-15-2010, 07:39 PM

Follow-up question:

How do you use the "startdate"?

In the sheet, it's linked in A3, what if I want to link the "startdate" in a different cell?

**geng** · 06-15-2010, 08:52 PM

Hi, the formula actually works, but there are some exceptions that needs to be considered. And I actually confused myself with the output that I wanted.

I attached a simple sample output so it will be easier to understand.

Sorry if the first file was so confusing.

In the attached file, I just want to return a data in the cells higlighted in yellow.
I already have the Latest Date and Previous Date working, all I need is the 2 previous book/date and 4 previous book/date.

Thanks!