Matching Data from one sheet to data on another and returning a value

**willmatic** · 05-12-2014, 08:54 PM

Hi i'm trying to get excel to look at DateTime on sheet 2, find the same datetime on sheet 3 then print the value for any column for the appropriate row. The formula i've used is in column G of sheet 2:
=IF(ISNA(INDEX(Sheet3!A2:G425654,MATCH(A2,Sheet3!$A$1:$A$425654,0),6)),"",INDEX(Sheet3!A2:G425654,MATCH(A2,Sheet3!$A$1:$A$425654,0),6))
file uploaded to mega:
https://mega.co.nz/#!fZdyATDK!CUQ71R...PoBrxM21PnoiAQ

I'm wondering if anyone can help me with a VBA solution as the nested function is not giving me the right results..

**FDibbins** · 05-12-2014, 09:55 PM

Hi and welcome to the forum

1st, just because the formula is not giving what you want, doesnt mean you need VBA. From the sounds of it, what you want is pretty standard stuff, and index/match sounds like the tool to use. On a side note, you could use IFERROR() instead of what you have there...
=IFERROR(INDEX(Sheet3!A2:G425654,MATCH(A2,Sheet3!$A$1:$A$425654,0),6)),"")

You say it is not giving the correct results, perhaps you need to absolute some of those ranges?
=IFERROR(INDEX(Sheet3!$A$2:$G$425654,MATCH(A2,Sheet3!$A$1:$A$425654,0),6)),"")

Also, not all members are able - or willing - to access files from file hosting sites, so please upload your sample WORKBOOK here

**willmatic** · 05-13-2014, 08:03 PM

Thanks for the welcome FDibbins, making the ranges absolute looks to solve the problem somewhat, i have millions of lines to run this on and the file sizes and processing time become outrageous, the smallest file i could manage was 3MB even with things stripped out.
i thought since vBA prints results this could help speed up the task and decrease file size.

**FDibbins** · 05-14-2014, 12:12 AM

VBA will should speed things up. I played with some of your formulas and streamlined a few things, see if this helps any...

Sheet2

G2=IFERROR(INDEX(Sheet3!$A:$F,MATCH(A2,Sheet3!$A:$A,0),6),"") copied down
H2=IF(Sheet3!E2="","",INDEX($E:$E,MATCH(Sheet3!$A2,$A:$A,0))) copied down and across

Sheet3

I deleted column D, and in the "new" column D, used this, copied down...
=IF(ABS(C2)>=6.5,1,0)

Not sure how often your data is updated, but if stays the same at the top of the tables, maybe consider valuing the answers?