Double VLookup or something else?

[adnan731]

Hi all,
Can someone help me?

All I want is: When I will enter some date in A3 to I3, % value should automatically come in box J3 to AA3, w.r.t A2 to I2

[Richard Buttrey]

Hi,

See attached where I've added a column A to hold the 1/1/2015 date in A3

[Bstack]

Hey Adnan -

you can use the Index function as an alternative to Vlookup. Using Index paired with Match will let you find the proper Percentage for the date you are interested in. Example, use this formula in cell J3 throught AA3. Formula: =INDEX($A$2:$I$3,1,MATCH(J1,$A$3:$I$3,0)).

You can throw an "If" statement in there so that if the value is not found, you get something more elegant than the default #N/A

[adnan731]

Hi,
Sorry. My problem is not solved yet.
If I put any date under Step 1, it should accordingly enter %. And total should be 100%.
I have removed some formulas, and put 0% there to show what result I want
Also, if I put e.g. 16-Feb-15 in J3, N3 should be 20% (10% because of B3 & 10% because of J3)

[Glenn Kennedy]

Try this. I have added a cumulative % row, which can be hidden.

[adnan731]

Hi,
I appreciate your response. That formula works well. But not fully.
In your above sheet, in F4 if I enter e.g. 12-Apr-15 instead of 24-Mar-15, it will give 50% upto 10-Apr-15 week and nothing after.
As far as i have observed, if there is any date is last cell, e.g. J4, it gives all result very well.
Thanks

[Glenn Kennedy]

It was 100% INTENTIONAL that it would only give % completion values up to the last entered date. You never told us what you want this for; but I assume that it is for "live" project progress tracking against objectives.

Let's say that today is 12th April and you have just been told that step 5 is complete. Youu put 12/4/15 in F4 and the overall project completed at 10/4/15 goes to 50%. Beyond that it is blank because it is in the future. You have NO IDEA if the project will be 50%, 60% or 100% complete by 17th April (the next reporting cell (V4) - so why put misleading information in it? Sooner or later the information will change.

But if that is what you REALLY want, then use this in K4 and drag across:

=INDEX($A$3:$J$3,MATCH(K1,$A$4:$J$4,1))

But... before you do, make sure that this is what you really need!

[dan4ish]

I have slightly modified the sheet,Please have a look at it.

[adnan731]

If today is 12th April and I have just been told that step 5 is complete. I put 12/4/15 in F4 and the overall project completed at 10/4/15 goes to 50%. Fine. and at 17/04/15 it should be 60%.. So the previous one doesn't serve the cause. But the above formula serves the purpose well in all cases.
(Well I needed both things. I have planned dates for all 9 steps. and with time I will get my actual dates. So, I am sorry if I couldn't explain it properly.)
Thanks alot for your cooperation. Cheers !!

[Glenn Kennedy]

Thanks for the Reputation that you have added. It's much appreciated. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[adnan731]

Yes marked. Thanks once again !!