IF <=0 INDEX MATCH LARGE otherwise return blank

Hi,

I have a nice little formula that does what I want but I can't get it to return nothing (blank) if it sees a value of 0 (zero) or below.

Right now it returns everything even if 0 or below. I want to to be blank it it finds "<=0" so it would only return anything above a zero value.

Here's my base formula.

Please Login or Register  to view this content.

Thanks for any help!

Try

=MAX(0,INDEX($B$1:$XR$1,MATCH(LARGE($B319:$XR319,COLUMN(A5)),$B319:$XR319,0)))

and format cell as CUSTOM 0;;;@

NOTE: the "blank" cells will still contain a value of 0 or less so this may be a problem if cells are used in other calculations.

Thanks everyone. Unfortunately all the solutions are still returning the zeros.

To make it easier to understand my use case, attached is a sample workbook with the formulas included and comments in the cells that should be blank.

Sorry I should have included this in the first place.

Thanks so much!

in M2

=MAX(0,INDEX($B$2:$H$2,MATCH(LARGE($B7:$H7,COLUMN(D1)),$B7:$H7,0)))

returns a value of 0.0001

so M2=0(.0001) is correct

Format cells in J2:M5 as number with 6 decimal places to see result.

Maybe try

=MAX(0,ROUND(INDEX($B2:$H2,MATCH(LARGE($B7:$H7,COLUMN(D1)),$B7:$H7,0)),3))

Thank you that works. Unfortunately it doesn't return my LARGE first then the zeros. It returns whatever it sees first.

e.g. B2=0 C2=0 D2=1... your solution will not return D2 first. It will return it last since it is in last place.

My original formula returns largest first, second largest second, etc. (and makes concessions for a tie breaker) all the way down to 0. Yours seems to return in order found, disregarding the LARGE first order. I hope I'm making sense.

Thanks again!

Looks like your second formula works as intended! Thanks!

I'll mess around with it and report back in a few mins.

Thanks again John. It looks like it works.

One last thing, for O-R I am trying to return the corresponding B1-H1. I can't seem to get there though.

I tried this:

HTML Code: 

Try


in O2 and copy across and down

=IFERROR(INDEX($B$1:$H$1,,MATCH(J2,$B2:$H2,0)),"")

Just spotted there are duplicate values (9) with different headers so the MATCH will only find the first header....

So you need to find someway of differentiating duplicate values (repeated in row 5 with values of 5).

Yea I saw that.... I have excel mind numb right now... I'll try later and post here if I find something that works.

Adding rep for ya on the first solution. Cheers....

A "standard" way of addressing this problem is to add a small amount onto each result so we get (for example) 9.0001, 9.0002.

This then throws the test for 0 out of the window !!!

A "standard" way of addressing this problem is to add a small amount onto each result so we get (for example) 9.0001, 9.0002.

This then throws the test for 0 out of the window !!!

I'll too will have think about how we might do this but will sign off for tonight (UK time!).

I'm back on this one.

I think your idea might work on the headers but I'm not sure how I would apply a helper value along side text and return the header.

Any help would be much appreciated.

I thought the file in Post #5 addressed the problem ????

Yes, thank you, for the first part (return only values with no zeros, not duplicated)

Post #11 onward refers to returning the header values from the results... headers are duplicating.

Headers are not duplicated ... unless I completely mis-understand.

Helper data in b7 onwards

Looks like the spreadsheet you attached reflects my old formula of calculating this which returns zero values (I wanted to return everything but zero values - blank cells if zero).

You proposed we use this formula instead for J2-M5:

Please Login or Register  to view this content.

Then format J2-M5 with this:

Please Login or Register  to view this content.

So that works well.

However, I am having a hard time returning the corresponding header value from J1-M1 in O2-R5 while leaving zero values blank cells.

Your proposed solution was this:

Please Login or Register  to view this content.

But that returned duplicates.

Cheers!

The only way is do as you did originally with your formulas in B7:H10 as we have to have a unique value for each of the data in B2:H5 and using this in O2 onwards

=INDEX($B$1:$H$1,MATCH(LARGE($B7:$H7,COLUMN(A1)),$B7:$H7,0))

Using this in J2 etc will remove the 0s with the 0;;;@ formatting:

=INT(INDEX($B2:$H2,MATCH(LARGE($B7:$H7,COLUMN(A1)),$B7:$H7,0)))

Thanks for the help JohnTopley.

I decided to use O2 onward as a helper and create a new table that doesn't return the zeros as follows - and it seems to work fine for the headers:

Please Login or Register  to view this content.

I can now build a new spreadsheet and bring in the results of the first helper table and second helper table.

I think this is now solved.

Again, thanks for hanging in there and helping on this.

Glad you have found a satisfactory solution.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.