+ Reply to Thread
Results 1 to 3 of 3

Matching value in two arrays

  1. #1
    Registered User
    Join Date
    11-01-2010
    Location
    London,England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Matching value in two arrays

    Hello I know there are lots of questions about arrays and matching on here but I couldn't find one that I was looking for. I have many arrays with values. What I would like to do is just find a matching value in each of them, if there is one. For instance if I had the arrays array1= [1 2 3] array2= [2 3 5] and array3= [5 6 7] I would like to return the following values
    match between array 1 and 2 is 2
    between 1 and 3 is 0
    between 2 and 3 is 5

    If there is more than one match I only need one. I have tried playing around with the index and match functions but I am having trouble with it

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Matching value in two arrays

    It sounds like you area actually comparing ranges, not arrays.
    If that's true, then...
    With
    Please Login or Register  to view this content.
    This formula returns the first range_1 value with a corresponding value in range_2:
    Please Login or Register  to view this content.
    This one compares range range_2 to range_3:
    Please Login or Register  to view this content.
    and this on compares range_1 to range_3:
    Please Login or Register  to view this content.
    In the above example the formulas return these values:
    2
    5
    No match

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    11-01-2010
    Location
    London,England
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Matching value in two arrays

    Well I actually was looking for arrays not areas but that doesn't really matter because this code still works. Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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