# EXCEL Help? LARGE and VLOOKUP **URL:** \ **Category:** Factual Questions\ **Created:** [July 14, 2010, 8:39pm UTC](https://boards.straightdope.com/t/excel-help-large-and-vlookup/546711 "2010-07-14T20:39:13Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [July 14, 2010, 8:39pm UTC](https://boards.straightdope.com/t/excel-help-large-and-vlookup/546711/1 "2010-07-14T20:39:13Z")
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: ```auto 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. ```auto 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 ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [July 14, 2010, 9:03pm UTC](https://boards.straightdope.com/t/excel-help-large-and-vlookup/546711/2 "2010-07-14T21:03:06Z")
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. ---
**Author:** ![mbetter](https://avatars.discourse-cdn.com/v4/letter/m/53a042/32.png) [@mbetter](https://boards.straightdope.com/u/mbetter)\ **Post date:** [July 14, 2010, 9:06pm UTC](https://boards.straightdope.com/t/excel-help-large-and-vlookup/546711/3 "2010-07-14T21:06:21Z")
Read this: [http://www.cpearson.com/excel/Rank.aspx](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. ---
**Author:** ![zev\_steinhardt](https://avatars.discourse-cdn.com/v4/letter/z/97f17d/32.png) [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)\ **Post date:** [July 14, 2010, 9:07pm UTC](https://boards.straightdope.com/t/excel-help-large-and-vlookup/546711/4 "2010-07-14T21:07:09Z")
Thanks. Actually, I found an answer. The third suggestion given [here](http://excel.tips.net/Pages/T003077_Looking_Up_Names_when_Key_Values_are_Identical.html) (assigning a unique rank number using RANK and COUNTIF to each row) seems to work. Zev Steinhardt