Index match ignoring zero values (help)

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
605
How can I make this formula ignore blanks and zero values?
Code:
=INDEX(O9:BV9,MATCH(MIN(O10:BV10),O10:BV10,0))
Thanks,
Pujo
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

ExcelElliott

New Member
Joined
Mar 1, 2018
Messages
41
Hi Pujo

In place of MIN(O10:BV10), try AGGREGATE(15,7,(O10:BV10)/((O10:BV10<>0)*(O10:BV10<>"")),1)

Hope that helps.
 

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
605
Thanks Elliott however, this does not work for me. I am still using Excel 2010 (i know)...
I get errors in the formula.
 

ExcelElliott

New Member
Joined
Mar 1, 2018
Messages
41
Ok, try the following

MIN(IF(O10:BV10<>0,IF(O10:BV10<>"",O10:BV10)))

Confirm by pressing Ctrl + Shift + Enter.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
39,108
Office Version
365
Platform
Windows
The Aggregate formula should work for 2010

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">O</td><td style=";">P</td><td style=";">Q</td><td style=";">R</td><td style=";">S</td><td style=";">T</td><td style=";">U</td><td style=";">V</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">T</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Data</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">N10</th><td style="text-align:left">=INDEX(<font color="Blue">O9:BV9,MATCH(<font color="Red">AGGREGATE(<font color="Green">15,7,(<font color="Purple">O10:BV10</font>)/(<font color="Purple">(<font color="Teal">O10:BV10<>0</font>)*(<font color="Teal">O10:BV10<>""</font>)</font>),1</font>),O10:BV10,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,096,387
Messages
5,450,108
Members
405,587
Latest member
djay52

This Week's Hot Topics

Top