Microsoft Office Forums Vlookup - if the result is blank then enter a formula

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-28-2019, 09:19 PM
srsev6 srsev6 is offline Vlookup - if the result is blank then enter a formula Windows 10 Vlookup - if the result is blank then enter a formula Office 2013
Novice
Vlookup - if the result is blank then enter a formula
 
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 Vlookup - if the result is blank then enter a formula Windows 8 Vlookup - if the result is blank then enter a formula Office 2016
Expert
 
Join Date: May 2017
Posts: 463
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 Vlookup - if the result is blank then enter a formula Windows 10 Vlookup - if the result is blank then enter a formula 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 Vlookup - if the result is blank then enter a formula Windows 10 Vlookup - if the result is blank then enter a formula Office 2013
Novice
Vlookup - if the result is blank then enter a formula
 
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 Vlookup - if the result is blank then enter a formula Windows 10 Vlookup - if the result is blank then enter a formula Office 2013
Novice
Vlookup - if the result is blank then enter a formula
 
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 Vlookup - if the result is blank then enter a formula Windows 8 Vlookup - if the result is blank then enter a formula Office 2016
Expert
 
Join Date: May 2017
Posts: 463
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
Vlookup - if the result is blank then enter a formula 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
Vlookup - if the result is blank then enter a formula How To Move Focus From VLOOKUP Result to Report Sheet swindon.expat Excel 2 04-10-2016 12:15 AM
Vlookup - if the result is blank then enter a formula formula, 2 criteria, one result cjdstephenson Excel 4 06-02-2015 09:17 PM
Vlookup - if the result is blank then enter a formula 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 02:24 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft