Consolidating rankings from 3 different sources

Hi and welcome to the forum

I would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

OK just so I'm clear on this...which sheet do you want to have the formula on?

OK see if this is what you want?

In D3, copied down (for J and P as well), use this...
=IF(C3="","",VLOOKUP(C3,'Ranks 1'!$A$1:$B$200,2,0))
for the other 2 sheets, just change the sheet name

I noticed that at least 1 name (Robert Griffin III, WAS) has an unprintable character in it, between the Griffin and the III. It looks like a space , but it's not a space that excel recognises - probably as a result of copying from another program.

That was the only name I could find like that, you can just delete the 2nd "space" and put it back in again.
OR if you will be importing data often, use this in rank 1 etc C1, copied down...
=SUBSTITUTE(A62,CHAR(160),CHAR(32))
you can then copy/paste values over the top of your original data

Happy to help, and Im really really happy your 1st time was good for you