two tables, closest date and time matching

[sknalodz]

Dear All,

i am facing issue with the formula.
i have two tables, on worksheet1 there is a list of dates in column A.
on column B i would like to get the grade from worksheet PP column B.
I started to do just simple formula to match closest date and time.
it works in some rows but sometimes i get repeating result like in the row 2825.

can you please tell me what is wrong?
if the formula works, adding vlook to find a grade is a piece of cake, i hope

attached an example file.


ExampleDate.xlsx

[azumi]

Maybe :

Changing this highlited area :

=IFERROR(INDEX(PP!$A$2:$A$37,MATCH(MIN(ABS(PP!$A$2:$A$37-Sheet1!A2)),ABS(PP!$A$2:$A$37-A2),1)),"")

with this

=IFERROR(INDEX(PP!$B$2:$B$37,MATCH(MIN(ABS(PP!$A$2:$A$37-Sheet1!A2)),ABS(PP!$A$2:$A$37-A2),0)),"")

[sknalodz]

thanks for your reply.
but it does not give such result as i want to get.
i mean only on closest date info about grade...

instead of it grades are listed everywhere...

[FlameRetired]

I interpreted your original post the same way azumi did.

Now I'm confused.

So, I'll ask....was limiting your INDEX/MATCH range (PP!$A$2:$A$37) to 36 rows your intent?

[sknalodz]

Hi,


Sheet1 has a list of dates.
Sheet PP has a list of grades produced on some dates.
i want to match list from PP to Sheet1.
If i use formula azumi, on a Sheet1 close to dates i got grades through all time not exact or close to dates from PP.
please find attached file.



ExampleDate_1.xlsx