+ Reply to Thread
Results 1 to 9 of 9

Match position of two different values from two tables

  1. #1
    Registered User
    Join Date
    11-11-2013
    Location
    Bucharest
    MS-Off Ver
    2016
    Posts
    19

    Post Match position of two different values from two tables

    Hello,

    I have 2 tables (same x/y) with various values (which may repeat), and I need a formula which returns 1 if:
    - it finds value 1 in table 1
    - it finds value 2 in table 2
    - the position of the 2 values within the 2 tables is identical

    I'm adding an overly simplified example sheet of what I need. It's over simplified because the 2 tables I'm actually comparing are much larger and the values inside them change according to data I add in a separate place, so I basically need this formula to track the changes that I make..

    Is it possible?
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Match position of two different values from two tables

    I could not figure out how to do this with formulas (it may be possible). Here is a macro solution. The function will recalculate if you change either of the two values you are looking for, or the contents or size/location of either table. It assumes that the tables are the same size.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-11-2013
    Location
    Bucharest
    MS-Off Ver
    2016
    Posts
    19

    Re: Match position of two different values from two tables

    This is nice and elegant.. I haven't dabbled with macros much, but I'll try to implement it on my sheet when I get home and let you know if I'm successful.

    Another thought came to mind: in case multiple matches are found, what should I change within the macro for it to count them all instead of returning 1?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,331

    Re: Match position of two different values from two tables

    Are you still using Excel 2007?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Match position of two different values from two tables

    This version will return a count of all matches:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-11-2013
    Location
    Bucharest
    MS-Off Ver
    2016
    Posts
    19

    Re: Match position of two different values from two tables

    Quote Originally Posted by AliGW View Post
    Are you still using Excel 2007?
    2016 actually

    @6StringJazzer - I can not thank you enough :D

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Match position of two different values from two tables

    Quote Originally Posted by Asth View Post
    2016 actually
    Please update your profile when you get a moment. Thanks.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Match position of two different values from two tables

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.

  9. #9
    Registered User
    Join Date
    11-11-2013
    Location
    Bucharest
    MS-Off Ver
    2016
    Posts
    19

    Re: Match position of two different values from two tables

    Done, done and done. I'll be back if I run into trouble again :P

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] INDEX / MATCH using multiple tables depending on values?
    By nostrum in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-21-2019, 05:15 AM
  2. How to match values from 2 different tables
    By luis6777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2016, 10:56 PM
  3. Replies: 1
    Last Post: 07-20-2016, 03:36 PM
  4. Match Values to Label Between 2 Tables with Tolerance
    By hydronicengr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-20-2015, 02:23 PM
  5. Look at multiple criterias to see if table values match values of other tables
    By gaudi93080 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2014, 06:45 PM
  6. [SOLVED] Match text values between two tables
    By awsz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-10-2013, 07:41 AM
  7. match values between tables
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2010, 12:25 PM

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