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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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

I
  • Question Question
Matching Multiple Criteria to Extract a Date
Replies
4
Views
93
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
509
Peter_SSs
Peter_SSs
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
385
Peter_SSs
Peter_SSs
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf
K
  • Question Question
VBA code to reference worksheet second from the end
Replies
1
Views
194
John_w
J

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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