# Combine VLookups and MAX/MIN functions?

#### Excel_ZM

##### New Member
Does anyone know how to combine vlookups and max/min functions?

Below is an example of my data.

 Data Sample Type Begin End a 0 10 a 10 20 a 20 30 b 30 40 b 40 50 b 50 60 c 60 70 c 70 80 c 80 100 d 0 5 d 5 12 d 12 22 d 22 35 Desired Result: Type List Min Begin Max End a 0 30 b 30 60 c 60 100 d 0 35

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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Weazel

##### Well-known Member
maybe something like...

Excel 2013
 A B C 1 Data Sample 2 Type Begin End 3 a 0 10 4 a 10 20 5 a 20 30 6 b 30 40 7 b 40 50 8 b 50 60 9 c 60 70 10 c 70 80 11 c 80 100 12 d 0 5 13 d 5 12 14 d 12 22 15 d 22 35 16 17 Type List Min Begin Max End 18 a 0 30 19 b 30 60 20 c 60 100 21 d 0 35

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

It worked!
Thank you

#### Excel_ZM

##### New Member
I never used the aggregate function before. I will look into this more.

##### MrExcel MVP
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.)

#### Excel_ZM

##### New Member

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.

#### Weazel

##### Well-known Member
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:

##### MrExcel MVP
...
would work in all versions but requires control shift enter and wouldn't be as efficient

Are you that sure? Is that also the case when you need a few more conditions?

#### Weazel

##### Well-known Member
sure enough in this case. i'm not sure what 'few more conditions' you're referring to.

#### Excel_ZM

##### New Member
Thanks again Weazel. I'll use the max and if combination.

Replies
1
Views
185
Replies
5
Views
260
Replies
6
Views
223
Replies
8
Views
195
Replies
2
Views
162

1,190,786
Messages
5,982,919
Members
439,807
Latest member
WXM86

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

### Which adblocker are you using?

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

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