INDEX MATCH formula ignoring 0's

[Gwill1983]

Hi all,

I have found several posts on here regarding finding the smallest number in a list and have tried to insert this information into my existing formula, but cannot make it work.

I reckon for an advanced user it will be a very simple formula adjustment.

My current formula is
=INDEX('Data Source'!$L$4:$L$55,MATCH(SMALL('Data Source'!$P$4:$P$55,G45),'Data Source'!$P$4:$P$55,0))

I would like this to remain the same, but only look for the lowest number that is greater than 0.

Can anybody help?

Thanks

Chris

 

[Domenic]

Try...

=INDEX('Data Source'!$L$4:$L$55,MATCH(SMALL(IF('Data Source'!$P$4:$P$55>0,'Data Source'!$P$4:$P$55),G45),'Data Source'!$P$4:$P$55,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Gwill1983]

Brilliant! I think I had got there, but didn't do the CONTROL+SHIFT+ENTER trick!

Thanks for your help

 

[Domenic]

You're very welcome!

Cheers!

 

[Ron Coderre]

For a non-CSE approach....try this:

=INDEX('Data Source'!$L$4:$L$55,MATCH(SMALL('Data Source'!$P$4:$P$55,G45+COUNTIF('Data Source'!$P$4:$P$55,0)),'Data Source'!$P$4:$P$55,0))

Does that help?

 

[ablazes]

For a non-CSE approach....try this:

=INDEX('Data Source'!$L$4:$L$55,MATCH(SMALL('Data Source'!$P$4:$P$55,G45+COUNTIF('Data Source'!$P$4:$P$55,0)),'Data Source'!$P$4:$P$55,0))

Does that help?

Ron,
I'm trying to follow along here...I'm bit of a novice...
If I'm looking for a value in a table (different sheet) that would be updated based on the changes in column name, can I use this?

For example, in my data tab I have column headers with month&year and row headers with department name. Is possible to alter this to give me a different value if I chnage the month/year combo in a different sheet?

Thanks!

 

[Ron Coderre]

Can you post some sample data so we can see what you're working with?