CODEWITHSUNDEEP
vbaexcel
hjh93
hjh93

Reputation: 570

Excel VBA Vlookup second most recent data for a value

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?

Upvotes: 1

Views: 666

Answers (1)

ashleedawg
ashleedawg

Reputation: 21657

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:

sample data

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:

example2

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

  • second-to-last (2016-12-17)

  • second-most-recent? (2014-12-17)

Upvotes: 2

Related Questions