UnitCostSampleDate.xlsx
I have tried many different Index Match combinations and I am not able to accurately achieve what I need. I need to pull UnitCost from Table2 into Table1 where Table1.Description2=Table2.PartNum AND Table1.Date(ColumnName)=Table2.OrderDate. The problem I am running into is the dates are not exact. I need it to go to the nearest value with still maintaining the criteria of Table1.Description2=Table2.PartNum. Concatenating the two columns does not work as it looses the ability to find nearest date.
Attached is sample date out of the two workbooks I am using.
Just a few trials
=INDEX(Table2$D$2:$D$65153,MATCH(C3,Table2$A$2:$A$65153, 0)) - Only one criteria, so it does not provide accurate results.
=INDEX(Table2$C$2:$C$65153,MATCH(AND(Table2$A$2:$A$65153=C6),("06/01/2012"<=[Table2][OrderDate]))
=IF(COUNTIF(Table2[PartNum],C5)=0,"-",MIN(IF(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5, 0), ABS(Table2[OrderDate]-"05/03/2012"))=MIN(IF(IFERROR(Table2[PartNum],0)=C5,ABS(Table2[OrderDate]-"05/03/2012"))),Table2[OrderDate])))
=MIN(IF(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5,0),ABS(Table2[OrderDate]-"05/03/2012"))=MIN(IF(IFERROR(VLOOKUP(C5,Table2[PartNum],1,FALSE)=C5,0),ABS(Table2[OrderDate]-"05/03/2012"))),Table2[OrderDate]))
Kind Regards,
Bookmarks