How to compare the variation of positions between two lists

[costalucas]

I have two lists, both with the same 196 names (all different from each other).


The two lists are distinguished only by the position in which each name appears. So, let's say that in the first list the name X is in the third row and in the second list it is in the fourth row. In this case X dropped one position, thus the variation in relation to the first list was -1.

I would like to know if there is a way to Excel compare the two lists and inform how many positions each name has dropped or rose.


Thanks!

[Sam Capricci]

one way is if they are last name, first name in one list and first name, last name in another then the easiest way is to use text to columns and/or formulas to reverse one of the lists so it looks like the other list.
But one thing I've found very helpful (but I don't know if it is available for Mac) is to download the fuzzy lookup by Microsoft for excel located here...
https://www.microsoft.com/en-us/down....aspx?id=15011
It seems to be only for platforms with windows so it might not be available for you.
For a more specific tailored response upload a specific workbook with enough examples that show what you have and leave out any confidential information, change names if necessary.

[costalucas]

Sambo, thank you for the answer.

I am uploading a workbook to give an example of what I want to do.
In column A I have the positions of constitutions from 1 to 10. And in column B I have the name of ten constitutions.
In column C I have the same ten constitutions, but in a different order.

In column D I have manually determined the variation of each constitution's position in column C compared to column B.
My question is: there is a way for Excel automatically determine this position variation?

[Ghozi Alkatiri]

at D2
=MATCH(C2,$B$2:$B$11,0)-MATCH(C2,$C$2:$C$11,0)

copy down

[costalucas]

This worked!

Thank you a lot!

[Sam Capricci]

well that isn't at all what I thought was being requested, it is good you posted the workbook. Glad Ghozi got you what you wanted.
don't forget to mark your post as solved using the thread tools dropdown at the top of the post.