Index match and ranges

[thomasclark]

Hello,


I have been stuck on this one for a while, any help would be greatly appreciated. Here is my data:


ID Name Test Test date Test score
223 Mike Test 1 05/01/2016 67
223 Mike Test 1 22/01/2016 92
223 Mike Test 1 07/01/2016 93
223 Mike Test 2 23/01/2016 91
223 Mike Test 2 17/01/2016 97


My aim is to compare scores between Test 1 and 2, to do this I want to look at the date of Test 1 and find Test 2 with the closet date, and return the date and score.

I did some research online and found a way to do this with an Index Match array formula. I have used this formula to look at Test 1, find the closet Test 2 date and return the date and score of Test 2.

Please Login or Register  to view this content.

This gives me...


ID Name Test Test date Test score Test 2 date Test 2 score
223 Mike Test 1 05/01/2016 67 17/01/2016 97
223 Mike Test 1 22/01/2016 92 23/01/2016 91
223 Mike Test 1 07/01/2016 93 17/01/2016 97
223 Mike Test 2 23/01/2016 91
223 Mike Test 2 17/01/2016 97


This works how I want it to, the problem is the data I am working with has thousands of individuals, all with a varying amounts of Test 1 and 2. Is there a way to get Excel to find the required range?


I have included an attachment which may make it clearer what I am trying to do. On the attachment I have adjusted the formula manually for the first two individuals.


Thanks

[CAntosh]

The formulas below are working for me on your sample set. They're array formulas (formulae?), so be sure to enter them in F2 and G2 with a Ctrl + Shift+ Enter, then fill down.

In F2:

Please Login or Register  to view this content.

And in G2:

Please Login or Register  to view this content.

Note: The formulas may be slow to calculate across thousands of entries, but they did calculate correctly for me.

[thomasclark]

Thanks a lot for you help.

I have copied your formulas into my data, it seems as though it has returned a blank cell in a few instances where it should have returned a date/score, I have reattached the mock data with your formulas included and highlighted these cells in red.

Could you please have a look?

Thanks again.

[CAntosh]

John and Sam haven't taken a Test 2 after those dates. What would you like to appear there?

[thomasclark]

Oh I see! I would like it to return the closest date to Test 1, whether it Test 2 was taken before or after. In my example, John's Test 1 was on 14/02/2016 so I would like it to return 12/02/2016 if this is possible?

Thanks again.

[CAntosh]

I think a combination of my formula and your original formula will do the trick. Enter the following with Ctrl + Shift + Enter and fill down:

In F2:

Please Login or Register  to view this content.

In G2:

Please Login or Register  to view this content.

[thomasclark]

This has worked perfectly! Thanks so much. I will mark this as solved.

Thanks again.

[thomasclark]

I've since come across a slight problem with this formula, this only appeared having applied it to my full data set.

When there is a Test 2 date that is the same as the Test 1 date, the formula in G2 brings back the score for Test 1. It then does this whenever the closest date for Test 2 is this duplicated date. As my data set is quite large this occurred a number of times and would take some time to fix manually. Any help to fix this issue would be greatly appreciated.

I have attached the mock data and highlighted the problem.

Thanks.

[JohnTopley]

Try this ..

in G2

=IF(F2<>"",INDEX($E$2:$E$12,MATCH(1,($B$2:$B$12=B2)*($D$2:$D$12=F2)*($C$2:$C$12="Test 2"),0)),"")

Enter with Ctrl+Shift+Enter

Copy down

[thomasclark]

Thanks very much, that has solved the issue. I will revert this back to solved.

Thanks again.

[thomasclark]

Unfortunately the modified formula in G2 has highlighted a new problem with the formula in F2, however I'm not sure what causes this problem. When the error occurs it returns the closest date from Test 1 rather than Test 2, I have been changing the dates in an effort to find out why this happens but I have had no success. I have attached some data showing the problem, I have highlighted the problem in red. I have also highlighted a date in purple, in this case the date appears to have been pulled from Tom's Test 1 date, rather than the closest date from John's Test 2 dates.

If anyone could offer some help it would be greatly appreciated.

[Czeslaw]

It may need?

[JohnTopley]

Try this in F2

=IF($C2="TEST 2","",INDEX($D$2:$D$14,MATCH(MIN(IF($B$2:$B$14=$B2,IF($C$2:$C$14="Test 2",ABS($D$2:$D$14-$D2),10^5),10^5)),IF($B$2:$B$14=$B2,IF($C$2:$C$14="Test 2",ABS($D$2:$D$14-$D2),""),""),0)))

[thomasclark]

Thanks very much, I inserted this into my main data set and everything worked as it should.

Thank you to all that helped.