I have a formula in an Excel 2010 workbook that wasn't generated by me that is causing me some confusion.  I'm not real familiar with using the VLOOKUP formula so if anyone can help that would be much appreciated.

I have a worksheet that is calculating planned start dates using a VLOOKUP formula pointed to our financial calendar worksheet:

=COUNTIFS(BSLN_Start, "> "& VLOOKUP(C21, Monthly_Calendar, 2, FALSE), BSLN_Start, "< " & VLOOKUP(C21, Monthly_Calendar, 3, FALSE), Milestone, "=No", Summary, "=No")

The Calendar worksheet contains a list of data fields that defines our financial reporting calendar using the following columns, Ex.

W/E Date   Month   Year   Year/WK

A typical record would show:

1/5/2001   January  2001  01/2001

1/12/2001 January  2001  02/2001

Now I want the formula to count a record every time the baseline_start date is > the start of our reporting period and <= the end of our reporting period.  How is the VLOOKUP formula figuring out the start/end dates of my reporting period?

Was really hoping for some assistance this week.  Not sure I've provided enough information or if there really is someone who can help me.  If anyone is familiar with VLOOKUP functions I could really use some help!

Not enough information.
A shared file would make it a piece of cake for us.
I for one would never use old-fashioned VLOOKUP, but the latest in
Tables, PowerPivot and/or Power Query.

I thought the same thing, but as I explained I did not develop this formula.  How would you solve?

Please see attached test file.  My question is on the 'Program_Start_Chart' worksheet.  The formula is expected to count the records in the 'Current_Week' worksheet where the Baseline_Start Date is greater than then accounting month 'start date' and less than the accounting month 'end date'

(Where the accounting month is derived from row 21 for each successive month).

I'll upload in the next response (don't see the option here on the response edit menu)

• Edited by DawnK2018 15 hours 10 minutes ago

The Formula above is counting entries in the BSLN_Start-area (column, named range?) where specific conditions are met. Some of the conditions are defined by using lookups.

They check whether the entry in cell "C21" has a match in the table/sheet "Monthly_Calendar" in the 2nd column and filter on everything that is bigger than that. In the second condition the VLOOKUP checks if that value is smaller than the values in the 3rd column.

So if you want to apply that logic for your new requirements, you need to make sure that in C21 you have the start date of your Reporting period, and check, whether the 2nd column of your Monthly calendar date is acutally the baseline start date. If not, adjust the number to the position (xth-column) where that date is.

Repeat with end date accordingly - almost: As you need to reference a different celll than C21 as this cannot contain start date as well as end date at the same time.

Have a look at a detailled expl. here: http://spreadsheets.about.com/od/excelfunctions/ss/vlookup.htm

Can't find how I share the file - any help would be appreciated

You cannot upload files here. You need to place them in a cloud storage and place the link in here.

Thanks so much for the help!!  Now that I understand that the 'Monthly_Calendar' was a different named range than my actual Calendar worksheet your explanation of the use of the VLOOKUP makes total sense.  I so appreciate your help Imke - your explanation was perfect even without having an example file from me!!  Thanks again!!