Vloopup for data in current month

[robmun50]

Hi,

I have a spreadsheet with a number of tabs on it.

On the Weekly Report tab I have a table that pulls that data from the Project Data Tab.

Key 7 Planview Milestone
Baseline Forecast Actual
1000 N.40000001 Proj 1
1001 N.40000002 Proj 2
1002 N.40000003 Proj 3
1003 N.40000004 Proj 4
1004 N.40000005 Proj 5
1005 N.40000006 Proj 6
1006 N.40000007 Proj 7



What I am looking for is a formula that looks up against the PPL Code and returns in the Forecast Column which 'Next Gate' is planned for this month (I have the current month and next month in cells I1 & I2 on the Weekly Report tab. If there is no gate planned for this month then I would like it to return 'No'

I've attached an example file for reference, usually the 'Project Data' tab has a list of many more projects, but for ease I have removed these from this attachment.

Thanks

[GeneralDisarray]

See attached.

I changed your data table to make it easier to author the formula in the "forcast" column.

[robmun50]

I'm actually after the 'Next Gate' column to pull through, rather than the date.

Also I've noticed that its only showing project 1000, even though the a couple of the others have dates in September.

[Jacc]

Anything like this?

Please Login or Register  to view this content.

I might have missed something cause my formula does not include the next month in I2.

[GeneralDisarray]

=IF(AND(INDEX(Table1,MATCH(B8,Table1[PPL Code],0),MATCH("Next Gate Date",Table1[#Headers],0))>=$I$1,INDEX(Table1,MATCH(B8,Table1[PPL Code],0),MATCH("Next Gate Date",Table1[#Headers],0))<=$I$2),INDEX(Table1,MATCH(B8,Table1[PPL Code],0),MATCH("Next Gate",Table1[#Headers],0)),"No")


I highlighted in red the changes I made. First, I forgot to dollar-sign the date min and max, and I matched the "Next Gate Date" column instead of "Next Gate".


See the attachment.