Excel formula to find date in given range (entire column)

I was actually trying to write a formula to match and find a date in the entire column. So say suppose I have something like this in sheet1:

  A   |     B
--------------------

 ID   |    Date1   
--------------------
101   |10/24/2018  
201   |01/21/2019  
301   |04/30/2018

And sheet2 contains ID and Date2 than the result column should look like:

 A   |      B      |      C   
---------------------------------

 ID   |    Date2   |    Result   
---------------------------------
201   |04/29/2018  |  FALSE
410   |04/30/2018  |   NA
101   |10/24/2018  |  TRUE
201   |03/29/2019  |  TRUE
101   |03/20/2018  |  FALSE
501   |04/30/2018  |   NA
301   |03/30/2018  |  FALSE
310   |04/30/2018  |   NA
310   |04/30/2017  |   NA

So, if Date1(10/24/2018) with ID 101 is greater than or equal to Date2(03/20/2018), than the result column should have false or else true. If either ID or date doesn't match/exists than it should be NA.

I tried with COUNTIF() and LOOKUP() but didn't workout! The formulas I tried looks like this:

=IF(COUNTIF(sheet2!A2:sheet2!A109944,sheet1!A2)>0,IF(COUNTIF(sheet2!B2:sheet2!B109944,">="&sheet1!B2),"False")),"True")

And Lookup formula:

=IFERROR(LOOKUP(2,1/(sheet1!A2=sheet2!A2:sheet2!A109944)*(sheet1!B2>=sheet2!B2:sheet2!B109944),"True")"False")

Can anyone please help me with this? Thanks in advance.

Raj

Posted 2019-05-23T10:27:17.813

Reputation: 33

Answers

You can do this with INDEX and MATCH:

The formula in D2:

=B2>=INDEX(Sheet1!$B$2:$B$4,MATCH(A2,Sheet1!$A$2:$A$4,0))

MATCH locates the ID on Sheet1 and INDEX returns the associated date. It then compares this to Date2 and returns a TRUE/FALSE result.

When there isn't an ID match, this returns the error condition #N/A , rather than NA.

If you really prefer NA, you can wrap this in IFERROR:

=IFERROR(B2>=INDEX(Sheet1!$B$2:$B$4,MATCH(A2,Sheet1!$A$2:$A$4,0)),"NA")

This will replace the #N/A with the NA text:

fixer1234

Posted 2019-05-23T10:27:17.813

Reputation: 24 254

Hi thank you for the solution. But this works partially. If the dates are equal it still shows wrong value. – Raj – 2019-05-30T07:33:33.230

@Raj, this exactly matches what you say in the question is what the result should look like. If it should be FALSE when they are equal, change the B2>=... to B2>... – fixer1234 – 2019-05-30T08:00:44.797

@fixer1234: If you don't mind, will this formula work if I wanted to compare the sheet1 dates against sheet2 dates? I mean I wanted the result column in sheet1 and compare each of the 3 dates in sheet1 against the 9 dates in sheet2. If you can help? – aCoder – 2019-05-30T10:44:31.527

@aCoder, Sheet1 is a lookup table with a single date for each ID. Sheet2 can have multiple dates per ID. So the logic of going in the other direction would be different. You would need to more precisely define how it is supposed to work (what goes on Sheet1 for the different Sheet2 dates, or how do you select from the Sheet2 dates to make a determination?). You can do almost anything in Excel, but that would be a different type of problem, requiring a different solution. – fixer1234 – 2019-05-30T17:29:51.057

Try this formula:

=IFERROR(IF(AND(COUNTIF(Sheet1!A:A,Sheet2!A2)=1,B2<VLOOKUP(A2,Sheet1!A:B,2,FALSE)),"False",IF(AND(COUNTIF(Sheet1!A:A,Sheet2!A2)=1,OR(B2=VLOOKUP(A2,Sheet1!A:B,2,FALSE),B2>VLOOKUP(A2,Sheet1!A:B,2,FALSE))),"TRUE")),"NA")

Lee

Posted 2019-05-23T10:27:17.813

Reputation: 1 382