=INDEX(B43:C57;SMALL(IF(B43:B57=B59;ROW(B43:B57)-ROW(B43)+1;ROW(B57)+1);COUNTIF(B43:B57;B59));2)

Assuming:

data range = B47:b57
cell b59 has the value/text that lookup will check on the range above.

enter this array formula with Crtl+Shift+Enter and excel will return the 2nd
column of the last occurence of the looked up.

hth

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"mbrewer41" escreveu:

I have a dataset containing speed-distance profiles of a group of vehicles.
The three categories of interest a vehicle number, speed, distance. I am
trying to create a list of the minimum and maximum speeds for each vehicle,
along with their corresponding distances. I can use a PivotTable to find the
min/max speeds for each vehicle, but I haven't found a good way to identify
the corresponding distances and put them in the table as well. Each vehicle
has multiple speed readings, so a basic VLOOKUP formula will only return the
last speed reading in the list for each vehicle. Is there a way to use
VLOOKUP in series (a nested VLOOKUP) to look for a vehicle number, then the
min/max speed in the profile of that vehicle, and then return the
corresponding distance?