Using array formula to 'vlookup' the lowest value in a list, avoiding blanks

[booms]

Hi all,

I've been trying to set up a formula to see vlookup the lowest value for a data set that will have multiple numeric values associated with a descriptor. I've found this array formula on this forum:

{=MIN(IF($A$2:$A$20=A2,$B$2:$B$20))}

That works great, except there are blanks in my data and I'm trying to avoid those. Is there any way to avoid this query returning a zero when it comes across a blank cell?

Thanks

 

[Peter_SSs]

You could use
{=MIN(IF($A$2:$A$20=A2,IF($B$2:$B$20<>"",$B$2:$B$20)))}

or, if Excel 2010 or later, this formula that doesn't require the Array formula entry.
=AGGREGATE(15,6,$B$2:$B$20/(($A$2:$A$20=A2)*($B$2:$B$20<>"")),1)

 

[Aladin Akyurek]

Also, alternatively if MINIFS() is available on your system...

=MINIFS($B$2:$B$20,$A$2:$A$20,A2)

 

[Peter_SSs]

Further comments.

Assuming no negative values in column B, if a cell in that column does actually contain a zero, and the column A value matches A2, all suggested formulas will correctly return 0 as the answer.

However, if all rows that match A2 in column A are blank in column B then MINIFS and the MIN(IF array formula incorrectly return 0 as the minimum whereas the AGGREGATE function returns an error that would allow you to handle that circumstance.

 

[Aladin Akyurek]

Too many qualifications can be a burden, but then...

=IF(A2="","not available",MINIFS($B$2:$B$20,$A$2:$A$20,A2))

 

[Peter_SSs]

Too many qualifications can be a burden, but then...

=IF(A2="","not available",MINIFS($B$2:$B$20,$A$2:$A$20,A2))

I can't see how that would work. I would have thought you would need something more like
=IF(COUNTIFS($A$2:$A$20,A2,B2:B20,"<>"),MINIFS($B$2:$B$20,$A$2:$A$20,A2),"not available")

or, if no MINIFS
=IFERROR(AGGREGATE(15,6,$B$2:$B$20/(($A$2:$A$20=A2)*($B$2:$B$20<>"")),1),"not available")

 

[booms]

Thanks very much guys - I used PeterSSs suggestion - works perfectly.

 

[Peter_SSs]

Thanks very much guys - I used PeterSSs suggestion - works perfectly.

Your very welcome.

 

[Aladin Akyurek]

Thanks very much guys - I used PeterSSs suggestion - works perfectly.

You could also indicate whether you have MINIFS or not...