Match position of two different values from two tables

1. 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?

2. 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.

3. 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. Re: Match position of two different values from two tables

Are you still using Excel 2007?

5. 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. Re: Match position of two different values from two tables

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

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

7. Re: Match position of two different values from two tables

Originally Posted by Asth
2016 actually

8. Re: Match position of two different values from two tables

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. 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

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