Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-28-2019, 09:19 PM
srsev6 srsev6 is offline Windows 10 Office 2013
Novice
 
Join Date: Feb 2019
Location: Illinois
Posts: 5
srsev6 is on a distinguished road
Default Vlookup - if the result is blank then enter a formula

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
File Type: xlsx Sample_S01.xlsx (11.9 KB, 4 views)
Reply With Quote
  #2  
Old 03-01-2019, 12:16 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 433
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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!
Reply With Quote
  #3  
Old 03-01-2019, 03:09 AM
bosco_yip bosco_yip is offline Windows 10 Office 2016
Novice
 
Join Date: Feb 2019
Location: Singapore
Posts: 7
bosco_yip is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-01-2019, 07:14 AM
srsev6 srsev6 is offline Windows 10 Office 2013
Novice
 
Join Date: Feb 2019
Location: Illinois
Posts: 5
srsev6 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 03-01-2019, 07:17 AM
srsev6 srsev6 is offline Windows 10 Office 2013
Novice
 
Join Date: Feb 2019
Location: Illinois
Posts: 5
srsev6 is on a distinguished road
Default

Thank you so much bosco_yip!
Reply With Quote
  #6  
Old 03-01-2019, 07:48 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 433
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Quote:
Originally Posted by srsev6 View Post
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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF Formula not returns result - Help Haha88 Excel 6 02-11-2018 02:07 PM
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula innkeeper9 Excel 2 09-13-2016 08:59 PM
How To Move Focus From VLOOKUP Result to Report Sheet swindon.expat Excel 2 04-10-2016 12:15 AM
formula, 2 criteria, one result cjdstephenson Excel 4 06-02-2015 09:17 PM
Fetching more than one result with VLookup on multiple sheets lilvillaf Excel 2 06-12-2014 05:39 PM


All times are GMT -7. The time now is 06:23 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft