I have a spreadsheet with forecast dates and completion dates. What I want to do is a vlookup to capture actual dates. However, if the result is blank then I want to enter a formula.

This is what I have but doesn't work:

=IF(VLOOKUP(A4,Sheet1!$A:$BF,30,0)=" ",V4+5,VLOOKUP(A4,Sheet1!$A:$BF,30,0))

and

=IFERROR(VLOOKUP(B5,Sheet1!$B:$BF,29,0),V5+5)


So in sheet1 is what I call the raw data. Sheet 2 has a vlookup to pull the information from sheet1. Now I need to the formula in sheet2. If the vlookup returns a blank then B2+5.



So in sheet2 cell C3, if the vlookup returns a blank then B3+5.

I have attached a sample spreadsheet.


Thank you in advance for your assistance.

Attached Files

Sample_S01.xlsx (11.9 KB, 12 views)

I don't see anything in column V on sheet2 ???!!!

But I see a major problem with your data structure! Currently you have 3 tasks. What happens, when you get 4th task? And what happens when the number of tasks will be about 100 or more? Unless it is an once only project, in best scenario, you have to redesign your tables continuously over many years!

My apologies, the "V" should be "B". The report isn't going to get any larger. This is one project. Any new projects have new reports created.

Whatever you like more!
Simply with different design you can have a single report of this kind, which works for long time. And probably some other reports (like yearly one) too. And you get an archive of tasks. I think some 10000-20000 tasks will be OK without slowing the workbook too much down.

Try….....................

In "Sheet2" B2, copied across and down :

=IFERROR(1/(1/VLOOKUP($A2,Sheet1!$A$2:$G$6,MATCH(B$1,Sheet1!$A$1 :$G$1,0),0)),A2+5)

Regards
Bosco

Thank you so much bosco_yip!