+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

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

    See if this come close.

    I made it with VLookup.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-25-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #5
    Registered User
    Join Date
    01-25-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1