Index min with multiple results, excluding zero's

Hi - I have s light issue with a formulae - it does work but I would like to expand it:

=INDEX(DATAPR1FIRM,MATCH(MIN(IF(DATAPR1HOURLY>0,DATAPR1HOURLY)),DATAPR1HOURLY,0))

I wanted to know how I would expand that formulae to get multiple results i.e. the formulae above locates the lowest value and pulls a corresponding cell (excluding zero's) but how would I get the second lowest and the third etc. If I need to create a new post please let me know. many thanks

You need to look into SMALL(IF for this.

Or post a sample workbook and we can show you how.


BSB

Ok - ill upload - please give me five mins

I hope this helps - many thanks

I have tried this but of alas it does not work -

=INDEX(DATAPR1ALL,SMALL(IF((DATAPR1HOURLY>0,DATAPR1HOURLY)),ROW(DATAPR1ALL)-MIN(ROW(DATAPR1ALL))+1),ROWS(AY$68:AY68)),1)

P

this is similar to this?

FIRM	REGION	MinHR
Firm 2	Edinburgh	100
Firm 3	Manchester	234
Firm 1	London	500
Firm 1	Leeds	600

Yes - so the ideal result would be
Firm 2 100
Firm 3 234
Firm 1 500
Firm 1 600
P

I understand - without region?

FIRM	MinHR
Firm 2	100
Firm 3	234
Firm 1	500
Firm 1	600

Done with PowerQuery aka Get&Transform (built-in feature in Excel 2016 Pro)

Hi - Thanks for the upload but there does not seem to be formulas behind the list you created. I also need to pull region or any other adjacent cell.

I am so sorry I seemed to have angered you. I really have not seen anything like what you have produced before so I would not know how to replicate it and I need to do that on multiple areas in the worksheet. I was just looking for a formulae as I think BadlySpelledBuoy was trying to achieve. Thank you for your help though - much appreciated.

P

Thanks to BadlySpelledBuoy - the solution works brilliantly with a helper column. Thanks once again!