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.

A	B	C	D
1	Name	Date	Previous Date for Name Match
2	Bob	5/8/16
3	Mike	5/15/16
4	Drew	6/13/16
5	Steve	6/15/16
6	James	6/25/16
7	Mike	7/1/16
8	Peter	7/30/16

<tbody>
</tbody>

 

[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.
[...]

Row\Col	A​	B​	C​	D​
1​	Name	Date	Name to Match	Prev Date
2​	Bob	5/8/2016​	mike​	5/15/2016​
3​	Mike	5/15/2016​
4​	Drew	6/13/2016​	​
5​	Steve	6/15/2016​	​
6​	James	6/25/2016​	​
7​	Mike	7/1/2016​	​
8​	Peter	7/30/2016​	​

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]

Row\Col	A​	B​	C​	D​
1​	Name	Date	Name to Match	Prev Date
2​	Bob	5/8/2016​	mike​	5/15/2016​
3​	Mike	5/15/2016​
4​	Drew	6/13/2016​
5​	Steve	6/15/2016​
6​	James	6/25/2016​
7​	Mike	7/1/2016​
8​	Peter	7/30/2016​

<tbody>
</tbody>

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.

Row/Col	A	B	C
1	Name	Date	Previous Date for Name Match
2	Bob	5/8/16
3	Mike	5/15/16
4	Drew	6/13/16
5	Steve	6/15/16
6	James	6/25/16
7	Mike	7/1/16	5/15/16
8	Peter	7/30/16
9	Mike	8/20/16	7/1/16

<tbody>
</tbody>

 

[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.