Excel 2007 vlookup with Rank

Posted on 2012-08-22
Last Modified: 2012-08-31
I have a list of clients ranked in descending order by their entry date into a program.

I need to find clients prior entry (if applicable). If client does not have a prior entry, I need  the default date to be clients only entry date.

For example,

Client 143 most recent entry to program was on 2/28/2012. I need a way to mark or identify the clients previous entry prior to 2/28/2012 entry. In this case the clients entry date would be 11/23/2011.
Question by:jbakestull
    LVL 92

    Expert Comment

    by:Patrick Matthews

    You wrote:

    Client 143 most recent entry to program was on 2/28/2012.

    What about the entry on Row 9, which has an entry date of 3/10/2012?

    Also, it's not entirely clear what you are trying to do here :)


    Author Comment

    shoot, bad sort.
    Client 143, most recent entry should be on 3/10/2012 which is row 11 (rank 7), I'm trying to find date value (rank 6) if applicable.

    I was thinking that by using max formula, I could find clients highest ranking value and then minus a one to get prior rank.

    For example, client 143 highest rank is 7, so 7 - 1 = 6.

    I'm just not sure how to use the vlookup.
    LVL 92

    Accepted Solution

    Please see the attached file:


    I used the following array formula in F2:


    To enter as an array formula, do not type in the curly braces, and use Ctrl+Shift+Enter instead of just Enter to finish it.

    I also applied this custom number format to Column F:


    That "hides" the zeroes.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now