+ 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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,568

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,600

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,568

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,568

    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
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,568

    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