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
jbakestullAsked:
Who is Participating?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.