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.

 

[sined192]

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"

Machine Number	Heat Mod Done
161122002040	Yes
161122002041	No
161122002042	No
161122002043	No
161122002044	No
161122002045	No
161122002042	Yes
161122002047	No
161122002048	Yes
161122002049	No

<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> </tbody>

 

[Aladin Akyurek]

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.

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

 

[sined192]

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

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.

Machine Number	Heat Mod Done
161122002040	Yes
161122002041	No
161122002042	No
161122002043	No
161122002044	No
161122002045	No
161122002042	Yes
161122002047	No
161122002048	Yes
161122002049	No

<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> </tbody>

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$11=D2),$B$2:$B$11)

where D2 = 161122002042.

 

[delta4957]

Thank you very much. This worked for me.

 

[Aladin Akyurek]

Thank you very much. This worked for me.

You are welcome. Great to hear that it works.