Combine VLookups and MAX/MIN functions?

Excel_ZM

New Member
Joined
Aug 16, 2016
Messages
24
Does anyone know how to combine vlookups and max/min functions?

Below is an example of my data.

Data Sample
TypeBeginEnd
a010
a1020
a2030
b3040
b4050
b5060
c6070
c7080
c80100
d05
d512
d1222
d2235
Desired Result:
Type ListMin BeginMax End
a030
b3060
c60100
d035

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
maybe something like...

Excel 2013
ABC
1Data Sample
2TypeBeginEnd
3a010
4a1020
5a2030
6b3040
7b4050
8b5060
9c6070
10c7080
11c80100
12d05
13d512
14d1222
15d2235
16
17Type ListMin BeginMax End
18a030
19b3060
20c60100
21d035

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B18=AGGREGATE(15,6,B$3:B$15/($A$3:$A$15=$A18),1)
C18=AGGREGATE(14,6,C$3:C$15/($A$3:$A$15=$A18),1)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
B18, just enter and copy down:

=MINIFS($B$3:$B$15,$A$3:$A$15,$A18)


C18, just enter and copy down:

=MAXIFS($C$3:$C$15,$A$3:$A$15,$A18)<strike></strike>

which should be super fast. (Note. This probably also holds for the array-processing versions.)
 
Upvote 0
Aladin,

yours did not work for me. maybe it's because I'm on excel 2010? I only see MAX and MAXA, MIN and MINA as options.
 
Upvote 0
minifs and maxifs are only available in 2016.

that said, Aggregate is only available in 2010 and newer

if you had an older version we'd have to use different formulas to get the desired outcome...

=MIN(IF($A$3:$A$15=A18,$B$3:$B$15)) control shift enter

=MAX(IF($A$3:$A$15=A18,$C$3:$C$15)) control shift enter

would work in all versions but requires control shift enter and wouldn't be as efficient
 
Last edited:
Upvote 0
sure enough in this case. i'm not sure what 'few more conditions' you're referring to.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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