2

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.

10
  • When you say File1 and File2, are they 2 different workbooks? If so, it is possible to add File 2 as a new sheet to the workbook that contains File1? Because then it will make it a lot easier to do what you're asking. Jan 24 '19 at 15:17
  • Can there be a maximum of one record in file 2 for which mydate falls within the begindate-enddate range?
    – fixer1234
    Jan 24 '19 at 20:34
  • @fixer1234: There can be more than one record in file 2. So 1 val1 in file1 should be compared with each matching val2 in file2
    – aCoder
    Jan 28 '19 at 7:47
  • @RowanRichards: yes they can be two different workbooks or two different sheets in the same workbook.
    – aCoder
    Jan 28 '19 at 7:47
  • It isn't clear, then, how you know what data to return from file 2 if a record in file 1 can match more than one record in file 2.
    – fixer1234
    Jan 28 '19 at 7:53
0

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)

enter image description here

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.

4
  • Hi teylyn. Thanks for the post. Which version of office you used? I'm using Office 365 and this formula doesn't seems to work for me.
    – aCoder
    Jan 28 '19 at 7:48
  • This formula will work in Excel 2007 and later. If you want to copy and paste the formula, you need the tables and columns set up exactly like in my example. If not, you need to replace the table and column names with references to your data in the A1 style. Have you done that?
    – teylyn
    Jan 28 '19 at 18:37
  • Yes I have replaced the table names according to my excel data. Say I have Val1 in column Z, than I would use column Z reference in the formula. But it din't work. Also I had a doubt. Since this formula uses lookup, I wonder how much time it would take for a million records comparison!
    – aCoder
    Jan 29 '19 at 5:53
  • If you want to process a million rows, then you may want to use a different approach. Power Query comes to mind. About not getting the formula to work: post a small sample file with just a few dozen rows on a free file sharing site like Dropbox or OneDrive. Nothing with ads, please. I'll fix the formula for you.
    – teylyn
    Jan 29 '19 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.