# How to find closest values in multiple columns and return adjacent values.

1. ## How to find closest values in multiple columns and return adjacent values.

In the attached file you will see that the date/time stamps in the 4 columns don't match up, so initially I need to identify which cells in the 3 columns on the right are closest to the one on the left. For example in column 1 we have 20/09/2012 01:23. In column 3 the closest value to that would be 20/09/2012 01:19, column 5 20/09/2012 01:23, and column 7 20/09/2012 01:18.

That is part 1 - identifying the closest value to the original set.

Part 2. Once this is done then I need to display the corresponding value to the right of the date/time. For example if the closest date/time in column 3 is 20/09/2012 01:19, the value I need to display would be 19.1

-----------------------------------------------------------------------------------------------
Essentially the whole point of this is I need to average the little numbers to the right of each time and date, but obviously need to identify the corresponding numbers in each column and match them up before I can average the 4 of them.

Any help would be AMAZING - I have been banging my head against a brick wall for days...Test.xls

2. ## Re: How to find closest values in multiple columns and return adjacent values.

See if this come close.

3. ## Re: How to find closest values in multiple columns and return adjacent values.

Patrician,

Welcome to the forum!
Using your example file and if I understand you correctly, the expected output should be 19.325 = (19.2+19.1+20.4+18.6)/4
If that is the case, then in cell J1 and copied down use this formula:
``Please Login or Register  to view this content.``

4. ## Re: How to find closest values in multiple columns and return adjacent values.

Amazing work guys, I will probably be using oeldere's formula for simplicity, but I have one small problem in that the formula doesn't allow for matching times. For example if I am looking for the number closest to 11:23pm in another column it won't return any exact matches, only the number closest to it. So if 11:23 does in fact exist in the second column, it will instead look for 11:24 or 11:22. Do you know what might be causing this?

Thanks again!

5. ## Re: How to find closest values in multiple columns and return adjacent values.

Amazing work guys, I will probably be using oeldere's formula for simplicity, but I have one small problem in that the formula doesn't allow for matching times. For example if I am looking for the number closest to 11:23pm in another column it won't return any exact matches, only the number closest to it. So if 11:23 does in fact exist in the second column, it will instead look for 11:24 or 11:22. Do you know what might be causing this?

Thanks again!

6. ## Re: How to find closest values in multiple columns and return adjacent values.

If you need an exact match, you can use the formula below.

J1 =
``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)