Multiple IF statements with VLOOKUP

I have read all the posts and goggled this but still cant seem to get it to work. I am trying to select a project through a lookup and then do an “if” to define the stage. I can do both, but not together. I need to combine these two statements:

I have a project tracking sheet I present to management, column A has a list of project (could be a different view of different project) and later columns have sequential dates which have to list the project phase (Define, Design, Develop, etc..) in the cell.
All data is in the Master tab. It will have the Project and the current dates for each project phase.

The VLOOKUP finds the project in Column "A" in the Master tab and the IF checks the various phase dates in the Master tab to find the project phase and assign it.


=IF(D4<='Master List of Projects'!$P$5,'Master List of Projects'!$O$2,IF(D4<='Master List of Projects'!$R$5,'Master List of Projects'!$Q$2,IF(D4<='Master List of Projects'!$T$5,'Master List of Projects'!$S$2,IF(D4<='Master List of Projects'!$V$5,'Master List of Projects'!$U$2,IF(D4<='Master List of Projects'!$X$5,'Master List of Projects'!$W$2,IF(D4<='Master List of Projects'!$Z$5,'Master List of Projects'!$Y$2,IF(D4<='Master List of Projects'!$AB$5,'Master List of Projects'!$AA$2)))))))

and

=VLOOKUP(A7,'Master List of Projects'!$B$5:$AH$53,1,FALSE)

Morning, I have posted a striped down sample doc link below. Basically what I am trying to do is combine the formulas from cell D6 and D7 into one cell.

Thank you.

https://docs.google.com/spreadsheets...gid=1306745872

Sample Reporting v4.xlsx

OK, I will upload it now.

Thanks.

In the IF statements the rows are hard coded but can not stay that way. As I stated, column A has a list of projects (could be a different view of different projects depending on the request), so the formula needs to read column A to match the project to the Master data tab to obtain the IF data.

If here is another way to do that I am open to suggestions.

I'm going to guess what you really want is an INDEX MATCH function,
Tell me if this is looking similar to what you want. You can now edit Column A to pull up values.

If you need anything changed or explained I'll be happy to.
If I did it wrong try and communicate better what you would like as the final result for a single column.

Red

This looks like it could work. What I want to return aren't the dates though, its the corresponding phase (Identify, Design, etc. in ROw2 of the Master Data tab) in the Dashboard cells. I can try and work with it and figure it out.

Thank you, very much.

The column has to be dates and the results the phase, not the opposite way.

I am not sure I can get this to work, the date in the Master Tab has to determine the Phase listed on the Dashboard Tab, that is why I used the IF statements.

AWESOME RED!!!! I will try it right now! Thanks much!!!

Red, one question. You have $E$5:$R$5 coded so it will look at that line in the Master tab for dates, which is project 1. How will it know to go to an different line dependent on the Project in Column A? Example the second project listed in the Dashboard is Project 27 in column A, it should look at the Master Tab and go to Row 31 for the dates.

OFFSET('Master List of Projects'!$E$5:$R$5,MATCH($A6,'Master List of Projects'!$B:$B,0)-5,0)

Sure, so we want to look at a different row depending on the project name in column A

MATCH($A6,'Master List of Projects'!$B:$B$,0)
This looks at $A6 = Project 1 and will change as the formula is dragged down.
It finds the Row # where Project 1 is in Column B. In this case the MATCH returns the number 5.

The OFFSET formula works like this:
OFFSET (RANGE , Row to change by, Column to change by)

Knowing that our match returns Row 5, we will use match to change this range by a certain number of rows, this makes it match the projects numbers.

My only issue with this right now is your input of dates. If there aren't all END dates written it won't work. I'm working on it now to figure out how to make it work in all cases.

Bebo, the projects are liable to change so using {5,7,9,11,13,15,17} hard coded won't be the best solution

I'm intent on using an OFFSET MATCH to get the right row but it's increasingly annoying to test it out. I'm nearing it though

OKAY.

This is the way I have done it.
I added a row below your phases on the master sheet so that the Identify, etc., was not merged. You should hide this row, do not delete the merged one.

The formula is a monster because of the lookup reference range I used to match the dates in the master list to the project name in the dashboard
OFFSET('Master List of Projects'!$E$6:$R$6,MATCH(Dashboard!$A6,'Master List of Projects'!$B:$B,0)-6,0)
let's call it ROWDATES)

=IFERROR(
INDEX('Master List of Projects'!$E$3:$R$3, , MATCH(

INDEX(ROWDATES,MATCH(Dashboard!D$5,ROWDATES,1)),

ROWDATES),
"")

I know it's super complicated but check out the sheet!

Red

All the end dates WILL be field out, it is part of our project requirements. Its looking great, I appreciate the quick response on this forum.

Looks like its working. I will review with my team tomorrow. Thanks again Red!!!!

Let me know if the last attached file has everything you need.
If not let me know~

If you are satisfied feel free to add reputation to me :D

Red

It's really clever,
It comes to different conclusions on the values than mine, and I'm a little confused...
Can you tell me what is going on in this sheet?
The top one is what I had and i tried including a bottom one with yours