Rank Function Problem

please replace the pox with a workbook:
To attach a Workbook
(please do not post pictures or links to worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Note: I selected cells D15:E18 and named them "ranktable".

In cell J4 and copied down:

Please Login or Register  to view this content.

Is that what you want?

In case you don't want to create a named range, the formula will be as follows.....

In J4

Please Login or Register  to view this content.

and copy down.

Notice that this is Vlookup for Approximate Match which is default if you leave the fourth argument range_lookup in the Vlookup formla (which is optional). But if you use Exact Match or 0 as fourth argument the formula will return #N/A because it doesn't find a exact match in the lookup table.