Excel Formula Help

I have a table listing of streets with house number ranges. I want to list each street as a single entry along with the lowest house number and highest house number on that street. There may be sev...

excel forum.xlsx15 KB

Formulas & Functions

Need Help

Jamil
Mar 22, 2018
Ok, If you do not have 365 then the below two formula will work in any version of Excel
both formulas below are array formula. so it cannot work if it is just entered.
when you enter the formula, Plz hold keyboard CONTROL SHIFT & ENTER.

For lowest put this and drag down

=MIN(IF($A$4:$A$14=A18,$B$4:$B$14))

and for highest put below formula and drag down

=MAX(IF($A$4:$A$14=A18,$B$4:$B$14))

If you do not like the control shift enter.

then if you have Excel 2010 or above then the following formulas will work just without the need of special keystroke.

For lowest

=AGGREGATE(14,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)

for Highest

=AGGREGATE(15,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)

Mark Broughton

Copper Contributor

Mar 22, 2018

The result comes up with #NAME? It appears this only works in Office 365. Unfortunately the company I work for does not have this version of Excel software.

Jamil

Bronze Contributor

Mar 22, 2018

Ok, If you do not have 365 then the below two formula will work in any version of Excel
both formulas below are array formula. so it cannot work if it is just entered.
when you enter the formula, Plz hold keyboard CONTROL SHIFT & ENTER.

For lowest put this and drag down

=MIN(IF($A$4:$A$14=A18,$B$4:$B$14))

and for highest put below formula and drag down

=MAX(IF($A$4:$A$14=A18,$B$4:$B$14))

If you do not like the control shift enter.

then if you have Excel 2010 or above then the following formulas will work just without the need of special keystroke.

For lowest

=AGGREGATE(14,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)

for Highest

=AGGREGATE(15,6,$B$4:$B$14/(($A$4:$A$14=A18)*($B$4:$B$14>0)),1)

Forum Discussion

Excel Formula Help