Vlookup - last match

[Exceler8]

I'm sure something like this has been discussed before... but I couldn't find anything.

I am essentially trying to do a Vlookup, but returning the last value to match, rather than the first. To be exact, here is what I have:

ID Value
1 10
2 10
3 10
4 10
1 20
3 10
1 40

I am trying to retrieve the most recent value, for the ID = 1. A conventional vlookup will give me the value "10"... but I want the value "40".

Any ideas?

TIA.

 

[Aladin Akyurek]

Unable to understand this, can you please give me some more accurate example for this?

Care to elaborate if you did not miss the following post: http://www.mrexcel.com/forum/excel-questions/201255-vlookup-last-match.html#post2464610.

 

[swingking03]

How can this be modified to include a dynamic range (i.e. how can lookup range stop at the cell/row above where the formula is)? If we use the original sample data, for row 4/ID3, how can the we look for A4 but only in B1:B3? I've tried changing it to =LOOKUP(2,1/(A1:A10=C1),B1:OFFSET(B10,-1,0)) but it doesn't seem to actually create a range.

ID Value
1 10
2 10
3 10
4 10
1 20
3 10
1 40

 

[Aladin Akyurek]

How can this be modified to include a dynamic range (i.e. how can lookup range stop at the cell/row above where the formula is)? If we use the original sample data, for row 4/ID3, how can the we look for A4 but only in B1:B3? I've tried changing it to =LOOKUP(2,1/(A1:A10=C1),B1:OFFSET(B10,-1,0)) but it doesn't seem to actually create a range.

ID Value
1 10
2 10
3 10
4 10
1 20
3 10
1 40

What is exactly what you are after? Would you describe the problem in words ( no formula) along with the expected result for the above sample in a1:b8 the headers included?

 

[swingking03]

What is exactly what you are after? Would you describe the problem in words ( no formula) along with the expected result for the above sample in a1:b8 the headers included?

Sorry for the confusion Aladin. What I would like to do is find the previous date for a name match. I have a spreadsheet that has rows of data added daily, so the range needs to be dynamic to accommodate future rows. For example, in cell D7, I would like to return 5/15/16 (from C3 via a name match in cell B3). And next week I might add another Mike, in which case I would like that new entry to return 7/1/16. I hope this makes sense.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Previous Date for Name Match
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Bob
[/TD]
[TD]5/8/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mike
[/TD]
[TD]5/15/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Drew
[/TD]
[TD]6/13/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Steve
[/TD]
[TD]6/15/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]James
[/TD]
[TD]6/25/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Mike
[/TD]
[TD]7/1/16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Peter
[/TD]
[TD]7/30/16
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sorry for the confusion Aladin. What I would like to do is find the previous date for a name match. I have a spreadsheet that has rows of data added daily, so the range needs to be dynamic to accommodate future rows. For example, in cell D7, I would like to return 5/15/16 (from C3 via a name match in cell B3). And next week I might add another Mike, in which case I would like that new entry to return 7/1/16. I hope this makes sense.
[...]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]Name[/td][td]Date[/td][td]Name to Match[/td][td]Prev Date[/td][/tr]
[tr][td]

2​

[/td][td]Bob[/td][td]

5/8/2016​

[/td][td]

mike​

[/td][td]

5/15/2016​

[/td][/tr]
[tr][td]

3​

[/td][td]Mike[/td][td]

5/15/2016​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Drew[/td][td]

6/13/2016​

[/td][td]

​

[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Steve[/td][td]

6/15/2016​

[/td][td]

​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]James[/td][td]

6/25/2016​

[/td][td]

​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Mike[/td][td]

7/1/2016​

[/td][td]

​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Peter[/td][td]

7/30/2016​

[/td][td]

​

[/td][td][/td][/tr]
[/table]

1. Define BigNum in FORMULAS | Name Manager as referring to:

=9.99999999999999E+307

2. Define Lrow as referring to:

=MATCH(BigNum,Sheet1!$B:$B)

Adjust the sheet name to suit.

3. Define Name as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

4. Define Date as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

With the above definitions in place...

In D2 control+shift+enter, not just enter:

=LOOKUP(BigNum,1/(INDEX(Name,1,1):INDEX(Name,MATCH(BigNum,1/(Name=$C2))-1)=$C2),
     INDEX(Date,1,1):INDEX(Date,MATCH(BigNum,1/(Name=$C2))-1))

 

[swingking03]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col
[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Name
[/TD]
[TD]Date
[/TD]
[TD]Name to Match
[/TD]
[TD]Prev Date
[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Bob
[/TD]
[TD]

5/8/2016​

[/TD]
[TD]

mike​

[/TD]
[TD]

5/15/2016​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Mike
[/TD]
[TD]

5/15/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Drew
[/TD]
[TD]

6/13/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Steve
[/TD]
[TD]

6/15/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]James
[/TD]
[TD]

6/25/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Mike
[/TD]
[TD]

7/1/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Peter
[/TD]
[TD]

7/30/2016​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


1. Define BigNum in FORMULAS | Name Manager as referring to:

=9.99999999999999E+307

2. Define Lrow as referring to:

=MATCH(BigNum,Sheet1!$B:$B)

Adjust the sheet name to suit.

3. Define Name as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

4. Define Date as referring to:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

With the above definitions in place...

In D2 control+shift+enter, not just enter:

=LOOKUP(BigNum,1/(INDEX(Name,1,1):INDEX(Name,MATCH(BigNum,1/(Name=$C2))-1)=$C2),
     INDEX(Date,1,1):INDEX(Date,MATCH(BigNum,1/(Name=$C2))-1))

Thanks so much again Aladin, this has put me on the right path but I don't think that I explained my goal very well. I don't have a column C that will be the reference for the match. I will be adding rows as time goes on that I want to be the source of the match. For example, if today I added row 7 (Mike: 7/1/16), then in C7, I would like to return 5/15/16 by searching for Mike in A2:A6. Then next week, say I add row 9 (Mike: 8/20/16), I would like C9 to return 7/1/16 by searching for Mike in A2:A8 and returning the last match. I would like for my search range to always be A2 through the row above the current row. I must also point out that my spread sheet has many more columns of data, but these two are the ones of note. If that changes things, let me know and I can provide the spreadsheet or a full example.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Previous Date for Name Match[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD]5/8/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mike[/TD]
[TD]5/15/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Drew[/TD]
[TD]6/13/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Steve[/TD]
[TD]6/15/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]James[/TD]
[TD]6/25/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mike[/TD]
[TD]7/1/16[/TD]
[TD]5/15/16[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Peter[/TD]
[TD]7/30/16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mike[/TD]
[TD]8/20/16[/TD]
[TD]7/1/16[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@swingking03

Select A1:C8.
Activate INSERT | table.

In C2 enter and copy down:

=IF(COUNTIFS($A$1:A2,A2)>1,LOOKUP(BigNum,1/($A$1:A1=A2),$B$1:B1),"")

The Table functionality allows you to enter records at will and takes care of running the formula for the new records.

 

[swingking03]

@swingking03

Select A1:C8.
Activate INSERT | table.

In C2 enter and copy down:

=IF(COUNTIFS($A$1:A2,A2)>1,LOOKUP(BigNum,1/($A$1:A1=A2),$B$1:B1),"")

The Table functionality allows you to enter records at will and takes care of running the formula for the new records.

Thank you so much Aladin! This worked like a charm.

 

[Aladin Akyurek]

Thank you so much Aladin! This worked like a charm.

You are welcome. Thanks for the update.

 

[sined192]

Hi All sorry for resurrecting an old thread but I thought it better than starting a new one.
I am trying to use the lookup function but I would like to display the last entry.
Could someone help please? All my data is formatted as text.