finding closest dat efor a test value

[Alex_a]

Hi Everyone,
I have two sheets: Sheet 1 with customer names in column A, test name (velocity) in column B, test date (for velocity) in column C, test value (for velocity) in column D. In sheet 2, same customer list is in column A, test name (strength) in column B, test date (for strength) in column C, test value (for velocity) in column D. I need to have a formula ( I guess array formula with index or something), so in Sheet 1 column E gives me the closet date/time in sheet 2 when the test for velocity test was measured. Thanks.

 

[p45cal]

In attached, formula in cell E2 is array-entered, meaning it's committed tio the sheet using Ctrl+Shift+Enter, not just Enter:
=INDEX(Sheet2!$D$2:$D$5365,MATCH(MIN((IF(Sheet2!$A$2:$A$5365=A2,ABS(Sheet2!$D$2:$D$5365-D2),FALSE))),(IF(Sheet2!$A$2:$A$5365=A2,ABS(Sheet2!$D$2:$D$5365-D2),FALSE)),0))

Formula in F2 is also array-entered:
=INDEX(Sheet2!$C$2:$C$5365,MATCH(MIN((IF(Sheet2!$A$2:$A$5365=A2,ABS(Sheet2!$D$2:$D$5365-D2),FALSE))),(IF(Sheet2!$A$2:$A$5365=A2,ABS(Sheet2!$D$2:$D$5365-D2),FALSE)),0))

There is an additional column G which gives the relevant row number on the other sheet. This column is not needed:
Array-entered in cell G2:
=MATCH(MIN((IF(Sheet2!$A$1:$A$5365=A2,ABS(Sheet2!$D$1:$D$5365-D2),FALSE))),(IF(Sheet2!$A$1:$A$5365=A2,ABS(Sheet2!$D$1:$D$5365-D2),FALSE)),0)

Also added column H, a clickable link to the relevant row in the other sheet, obviously can also be removed.
In cell H2, normally entered:
=HYPERLINK("#Sheet2!D" & G2,"link")

Copy down these formulae as far as you need.
Check they're right.
They take into account Customer List, but not TEST_NAME as you wouldn't get any results at all.
The formulae are quite resource-hungry, so don't try to include whole columns in any of these formulae, they'll take forever to re-calculate.

 

[Alex_a]

Thank you so much! You are the man.