Vlookup - last match

E

Exceler8

New Member
Joined
May 16, 2003
Messages
33
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.
 
Sort by date Sort by votes
Sorry I thought my post would include a picture giving all the required in formation.
I have a command button on a user form that uses the text in textbox1 of the user form to search the spread sheet, this works great but it returns the first entry.

Private Sub CommandButton1_Click()
Dim machine As String
Dim heatmod As String
machine = TextBox1.Text
heatmod = Application.WorksheetFunction.VLookup(machine, Range("$A:$C"), 2, False)
MsgBox machine & " heat mod done " & " = " & heatmod
End Sub

If I search 161122002042 I receive the message "161122002042 heat mod done = No"

[TABLE="width: 164"]
<colgroup><col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <tbody>[TR]
[TD="class: xl63, width: 115, bgcolor: transparent"]Machine Number[/TD]
[TD="class: xl63, width: 104, bgcolor: transparent"]Heat Mod Done[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002040[/TD]
[TD="class: xl63, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002041[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]161122002042[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002043[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002044[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002045[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: yellow"]161122002042[/TD]
[TD="class: xl63, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002047[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002048[/TD]
[TD="class: xl63, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]161122002049[/TD]
[TD="class: xl63, bgcolor: transparent"]No[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
sined192 said:
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.
Click to expand...

=LOOKUP(9.99999999999999E+307,A:A)

will display the last numeric entry from column A.

If column A houses text values...

=LOOKUP(REPT("z",255),A:A)

will display the last text entry from column A.

=LOOKUP(9.99999999999999E+307,1/(A:A=""),A:A)

returns any last value, except a blank. Should not be used if one of the foregoing would apply.
 
Upvote 0
Aladin Akyurek said:
=LOOKUP(9.99999999999999E+307,A:A)

will display the last numeric entry from column A.

If column A houses text values...

=LOOKUP(REPT("z",255),A:A)

will display the last text entry from column A.

=LOOKUP(9.99999999999999E+307,1/(A:A=""),A:A)

returns any last value, except a blank. Should not be used if one of the foregoing would apply.
Click to expand...

Hi Aladin thanks for your reply, I was hoping my original post would include an image to give all the relevant information but it didn't so I will try to explain what I am doing.
I have a user form that has a text box and the text entered is used to search the spread sheet, what I have upto now works great but it returns the first entry.

Private Sub CommandButton1_Click()
Dim machine As String
Dim heatmod As String
Dim dat As String
machine = TextBox1.Text
dat = Application.WorksheetFunction.VLookup(machine, Range("$A:$C"), 1, False)
heatmod = Application.WorksheetFunction.VLookup(machine, Range("$A:$C"), 3, False)
MsgBox dat & " " & machine & " heat mod done " & " = " & heatmod
End Sub

When I search 161122002042 I receive the message "161122002042 heat mod done = No" I would like it to say Yes.

[TABLE="width: 164"]
<colgroup><col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <tbody>[TR]
[TD="class: xl65, width: 115, bgcolor: transparent"]Machine Number[/TD]
[TD="class: xl65, width: 104, bgcolor: transparent"]Heat Mod Done[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002040[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002041[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]161122002042[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002043[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002044[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002045[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]161122002042[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002047[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002048[/TD]
[TD="class: xl65, bgcolor: transparent"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]161122002049[/TD]
[TD="class: xl65, bgcolor: transparent"]No[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
A search formula (like index match) that can pull the most recent date when multiple matches may occur based on the criteria figure.
Replies
2
Views
75
MARZIOTULLIO
M
J
  • Question Question
Index, Match, Large based on two Criteria
Replies
3
Views
91
Fluff
Fluff
I
  • Question Question
Matching Multiple Criteria to Extract a Date
Replies
4
Views
103
hagia_sofia
hagia_sofia
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
561
Peter_SSs
Peter_SSs
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
431
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,931
Messages
6,175,465
Members
452,646
Latest member
tudou

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top