I'm working on a huge data and I need to fetch data based on a cell value and a date. I do realize that my task can be done through a macro but I want a simple formula rather.
Problem: I have two excel files, say File1 and File2. The first one looks something like this: Dates are in MM/DD/YYYY
format.
A B C
-----------------------------------
Val1 myDate Result
-----------------------------------
AJSKD 12/12/1991
AJSKD 12/12/1992
AJSKD 11/10/1992
ASHDI 01/10/1992
And the second file contains:
A B C D
-----------------------------------------------------
Val2 data beginDate endDate
-----------------------------------------------------
AJSKD 21ASD 12/10/1992 12/31/1992
AJSKD 23AIO 10/10/1992 11/31/1992
ADSUI 21389 12/01/1993 02/21/1994
MKASI AS123 01/12/1994 04/01/1994
ASHDI 34AS1 01/11/1992 01/31/1992
Now what I want is, I want a filter on AJSKD
in File2, something like this: =IF(File1$A2=File2$A:A)
and when this is true, I want another criteria: =IF(AND(mydate>=beginDate,myDate<=endDate)
and when both the condition is true I want to copy the data into the result cell.
Consider an example: I want to fill the result column of A3. So I filter the records in File2
based on the value AJSKD
. On filtering I get:
A B C D
-----------------------------------------------------
Val2 data beginDate endDate
-----------------------------------------------------
AJSKD 21ASD 12/10/1992 12/31/1992
AJSKD 23AIO 10/10/1992 11/31/1992
But since myDate
associated with AJSKD
in File1 is 12/12/1992
. This date falls in between 12/10/1992
and 12/31/1992
, I want the data 21ASD
and not 23AIO
.
So the result should be like this:
A B C
-----------------------------------
Val1 myDate Result
-----------------------------------
AJSKD 12/12/1991 FALSE
AJSKD 12/12/1992 21ASD
AJSKD 11/10/1992 23AIO
ASHDI 01/10/1992 FALSE
Since I have a million records, I can't filter manually and search for the data. So I was trying to come up with the formula which would work on it. The second part works good, i.e., the date search. But I'm unable to build formula for the first part. Can anyone please point me in the right direction.
Thanks in advance.