EXCEL Help? LARGE and VLOOKUP

Factual Questions
1

I’m hoping someone can provide some insight into a particularly perplexing problem I’m having with Excel.

Let’s say I have some data:




A                            B
-------------------     ----------------
373                      Alexander
364                      Galvin
417                      Johnson
373                      Mathewson
363                      Spahn
511                      Young

What I would like to do is get the output to look like this without actually doing a sort.




C                            D
-------------------     ----------------
511                      Young
417                      Johnson
373                      Alexander
373                      Mathewson
364                      Galvin
363                      Spahn

I know I can order the data in column C using the LARGE function. And, if there were no duplicates in C, I could use VLOOKUP to populate D. The problem, however is that there are two names with a value of 373. As a result, VLOOKUP will populate D with “Alexander” (or “Mathewson”) for both values.

Is there a way to get the data in the format that I want, considering the fact that there are duplicate values in column A?

Thanks,

Zev Steinhardt

2

If you knew ahead of time that any duplicates would only be 2 or less, you could write a complicated ugly formula that combined COUNT(), MATCH(), and VLOOKUP() with a shifted range offset to ignore the first “373” you found.

The formula would be really ugly. It makes my head hurt to think of writing such a beast.

Maybe another easier way is to create a scratch worksheet with the sorting you need (no VLOOKUP() required) and then refer to those cells back in your “presentation” sheet.

3

Read this:

http://www.cpearson.com/excel/Rank.aspx

It’s using RANK() but I think the same idea would work with LARGE(), using a COUNTIF() to account for duplicates.

4

Thanks. Actually, I found an answer.

The third suggestion given here (assigning a unique rank number using RANK and COUNTIF to each row) seems to work.

Zev Steinhardt