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?

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

typo mistake

range b43 no b47 as I told you before
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

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

I'm not sure I explained my problem very clearly. Given the following sample
set of data:

Veh Spd Dist
1 43 210
1 44 215
1 46 435
1 48 515
1 47 585
2 39 197
2 40 250
....
107 51 690

Can I write an equation in Excel that will search for vehicle #1, then look
for the minimum speed and return the corresponding distance? I would copy
this equation and edit it to look for the max speed and distance for vehicle
#1, then copy both equations to find min and max speeds and distance for all
107 vehicles, producing this table:

Veh Min Spd Min Dist Max Spd Max Dist
1 43 210 48 515
2 xx xxx xx xxx
....
107 xx xxx xx xxx

Is this possible with a nested VLOOKUP?


"Marcelo" wrote:

typo mistake

range b43 no b47 as I told you before
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

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