Excel VBA Vlookup second most recent data for a value

Question

Currently working on an Excel worksheet with Vlookup. My reference table in Sheet 1 has a list of data along with its date, its name defined as ProjectEntry. For example:

-----------------------------------
| Project No | ID |  Service Date |
|------------|----|---------------|
|     01     | A1 |   10/12/17    |
|     02     | B2 |   13/12/17    |
|     01     | A1 |   14/12/17    |
|     03     | C3 |   14/12/17    |
|     01     | A1 |   16/12/17    |
-----------------------------------

Now my Vlookup in Sheet2 wants to lookup the second most recent date based on the ID to get the last service date. For example when I select ID = 01 , Vlookup = 14/12/17.

For the Vlookup formula I managed to get the first entered Service Date (10/12/17):

=VLOOKUP(I7,ProjectEntry[[#All],[ID]:[Service Date]],2,FALSE)

But I'm not sure how to get the 2nd most recent date for A1. What should I add to the formula to make it work?

ashleedawg · Accepted Answer · 2017-12-27 03:25:05Z

2

Instead of seeking the "second-to-last match" we could instead look for the "second highest match", in which case an array formula using the LARGE function will return what you need.

If your example data is arranged in A2:C6 like this:

then you could use this array formula to return the "2nd highest date" where the ID = A1 :

=LARGE(IF($B$2:$B$6="A1",$C$2:$C$6),2)

Your question said you need to look for ID = 01 but those are two different columns. If you instead need to look for `Project No = 01" then your array formula would be :

=LARGE(IF($A$2:$A$6="01",$C$2:$C$6),2)

...that's assuming that Project No is stored as text. If it's actually a number (formatted with a leading zero) then you would use :

=LARGE(IF($A$2:$A$6=1,$C$2:$C$6),2).

☆ Remember that since these are all ARRAY FORMULAS you need to specify that when entering the formulas; instead of using ENTER, finish entering the formula with:

CTRL + SHIFT + ENTER

EDIT:

To clarify whether this method will work, if the data is arranged like this:

...which value should be returned for ID = A1 (or Project No = 1)?

second-to-last (2016-12-17)
second-most-recent? (2014-12-17)

edited Dec 27, 2017 at 3:25

answered Dec 27, 2017 at 2:12

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

hjh93 Over a year ago

Well that depends on how the users use it. For all I know they may jump around here and there so I gotta be prepared for that.

Variatus Over a year ago

These formulas require that the dates be "true" dates (meaning numeric, not strings). They will throw an error if there was no second-largest, such as Project No. 03 in the question's example.

ashleedawg Over a year ago

@hjh93 - so an older date could be entered after a newer one, correct? Also to address Variatus's concern, I assume the dates are stored normally, not as text, correct? (As in, do the example formulas I gave work so far?)

ashleedawg Over a year ago

see my above edit (in my answer) to clarify what you need.

hjh93 Over a year ago

2nd to most recent.

|

Collectives™ on Stack Overflow

Excel VBA Vlookup second most recent data for a value

1 Answer 1

EDIT:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related