Thread: Index match ignoring zero values (help) Thanks: 0 Likes: 0

1. Index match ignoring zero values (help)

How can I make this formula ignore blanks and zero values?
Code:
`=INDEX(O9:BV9,MATCH(MIN(O10:BV10),O10:BV10,0))`
Thanks,
Pujo  Reply With Quote

2. Re: Index match ignoring zero values (help)

Hi Pujo

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

Hope that helps.  Reply With Quote

3. Re: Index match ignoring zero values (help)

Thanks Elliott however, this does not work for me. I am still using Excel 2010 (i know)...
I get errors in the formula.  Reply With Quote

4. Re: Index match ignoring zero values (help)

Ok, try the following

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

Confirm by pressing Ctrl + Shift + Enter.  Reply With Quote

5. Re: Index match ignoring zero values (help)

The Aggregate formula should work for 2010

Excel 2013/2016
NOPQRSTUV
9OPQRSTUV
10T110756

Data

Worksheet Formulas
CellFormula
N10=INDEX(O9:BV9,MATCH(AGGREGATE(15,7,(O10:BV10)/((O10:BV10<>0)*(O10:BV10<>"")),1),O10:BV10,0))  Reply With Quote

User Tag List

blanks, formula, ignore, index, values 