IF statement and Vlookup Query?

Hi,

I'm new to the forum and a definite novice compared to most on here. Anyway hope you can help with the following problem.

I'm basically need to do a complex vlookup statment but I'm not sure if it is the correct or easiest way to do it.

In my new spreadsheet I'm searching for a project I.D. eg. "1000".

In the source data spreadsheet the Project I.D. is in Column 1.

What I need to do is search the columns to the immediate right of the Project ID which have headings: "Milestone 1 Description" and "Milestone 1 Date". up to "Milestone 10 Description" and Milestone 10 Date". So 21 Columns in total including the project ID.

What I need to do is search along the row with Project I.D "1000" until I get a description "Project Completion". If this description is in the column "Milestone 3 Description" I then want the formula to return the details in "Milestone 3 Date".

Hope the above is clear and if so those anyone have an idea of how to do it?

Thanks in advance.

Hi Ross, and welcome to the forum.

You can do this with a single statement, but I usually break a problem down into parts and use what's called a "helper column" to store intermediate results. People have different approaches to helper columns; I usually stick mine out of sight, beyond column AA somewhere.

Using your naming, we'll have two sheets: Source and New.

So, the first step is to find your project ID in the source data sheet. In column AB of New (New!AB:AB) I'd have a series of formulae for as many projects as you want to look up, of the form =IF($A1<>"",MATCH($A1,Source!$A:$A,0),"") (ie, enter that in New!AB1 and copy it down as far as you need to go). Pick a larger number of rows than you need: say, 100 or 1000; it will ignore any rows where nothing is entered in column A.

Then, in the next column (New!AC:AC), we make use of that information to look across the correct row of your source for the correct column: =IF(ISNUMBER(AB1),MATCH("Project Completion",INDEX(Source!$A:$A,AB1):INDEX(Source!$U:$U,AB1),0),""). That will look for the text "Project Completion" in any of the first 21 columns. Copy this down also for as many rows as you did with the previous step.

Now you are ready to use this information. Next to your project ID in column A, you can have the date information you wanted in column B with: =IF(ISNUMBER($AC1),INDEX(Source!$A:$U,$AB1,$AC1+1),""). This will retrieve the data information from the column next to your "Project Completion" column.

There are more succinct ways of doing it, and I'd normally add a few bells and whistles (for example, row 1 might be a title row and you'd begin on row 2) but those are the basics and I find it helpful, especially with more tricky statements, to be able to analyse intermediate results as the final solution is being built.

Thanks ffffloyd and hasseb A for getting back to me so soon.

ffffloyd - you're method worked perfectly and was especially easy for me to understand too when you broke the formula down into 3 sections.

Hasseb A - I also tried your formula but it was returning the column heading eg. "Milestone 9 Date" and not the actual date.

Thanks though. Gave me a great insight into how index and match can be used in relatively complex formulae.