Mulitple IF statement with DATEVALUE & VLOOKUP

Posted on 2014-11-04
Last Modified: 2014-11-07
Could you please help I have the following formula that I need to add additional criteria to

=IFERROR(IF(VLOOKUP([@[Store '#]],ISS!$C:$HX,21,FALSE)="",DATEVALUE("01/01/3000"),VLOOKUP([@[Store '#]],ISS!$C:$HX,21,FALSE)),DATEVALUE("01/01/3000"))

I would like for statement to read If Field 23 is blank then use the date in field 21, if field 21 is blank then return 1/1/3000 else return date in field 23

I tried this function but got False  where I would like 1/1/3000 to populate: =IFERROR(IF(VLOOKUP([@[Store '#]],ISS!$C:$HX,23,FALSE)="",IF(VLOOKUP([@[Store '#]],ISS!$C:$HX,21,FALSE)="",DATEVALUE("01/01/3000")),VLOOKUP([@[Store '#]],ISS!$C:$HX,23,FALSE)),DATEVALUE("01/01/3000"))
Question by:jmac001

Expert Comment

ID: 40422812
Can you attach a sample of the spreadsheet?
LVL 26

Expert Comment

ID: 40423037
=IFERROR(IF(VLOOKUP([@[Store '#]],ISS!$C:$HX,21,FALSE)="",DATEVALUE("01/01/3000"),if(VLOOKUP([@[Store '#]],ISS!$C:$HX,23,FALSE)="",VLOOKUP([@[Store '#]],ISS!$C:$HX,21,FALSE)))"")
LVL 33

Accepted Solution

Rob Henson earned 500 total points
ID: 40423839
Prof JJ - missing comma before double quotes at end of formula.

How about, if not already covered, in logic syntax rather than formula:

IF ( AND (Lookup 21 = "", Lookup 23 = "") Then "01/01/3000" Else IF Lookup 23 = Blank Then Lookup 21 Else Lookup 23 )

All bound within an IFERROR (Formula, "")

Hope that makes sense and can be interpreted to formula.

Rob H

Author Closing Comment

ID: 40429145
Thanks Rob I was able to follow your solution. Prof JJ, Rob 's was the best solution, after trying the your solution it flipped the results that I had, populated dates changed to False and populated False to a date.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question