Gfrktyl

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.

Using tables called File1 and File2 and structured references instead of different files, this works as per the screenshot:

=IFERROR(LOOKUP(2,1/((File2[Val2]=[@Val1])*(File2[beginDate]<=[@myDate])*(File2[endDate]>=[@myDate])),File2[data]),FALSE)

You don't want to run this with whole column references. If you have "a million" rows, either turn them into a table and use structured references or use exact ranges for your data, like $A$1:$A$400000

Oh, and I took the liberty of correcting the 31st November to the 30th. My computer liked that better.