Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Excel 2007 vlookup with Rank

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.
Prior-Entry.xlsx
0
jbakestull
Asked:
jbakestull
  • 2
1 Solution
 
Patrick MatthewsCommented:
jbakestull,

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 :)

Patrick
0
 
jbakestullAuthor Commented:
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.
Prior-Entry.xlsx
0
 
Patrick MatthewsCommented:
Please see the attached file:

Q-27838419.xlsx

I used the following array formula in F2:

{=MAX(IF(($A$2:$A$31=A2)*($D$2:$D$31<D2),$D$2:$D$31,""))}

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:

mm/dd/yyyy;;

That "hides" the zeroes.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now