# Index Match Duplicate Values - ignore cells equal to 0

1. ## Index Match Duplicate Values - ignore cells equal to 0

Hi All,

Im using the following formula;

=INDEX(\$A1:\$H1,MATCH(LARGE(\$A2:\$H2-COLUMN(\$A2:\$H2)/10^10,COLUMNS(\$A5:A5)),\$A2:\$H2-COLUMN(\$A2:\$H2)/10^10,0))

I have a row of data of scores ranging from 0 to 4. Some cells have the same score. I am attempting to index match so the highest 2 scores will return the header / corresponding team - without duplicating.

Where I am stuck, I do not want the 0 score to be included in this formula - I dont want in those 2 highest scores for one to be a 0.

How can I manipulate this formula to only calculate scores > 1 ?  Register To Reply

2. ## Re: Index Match Duplicate Values - ignore cells equal to 0

"Some cells have the same score."

You can solve this if you make a helpcolumn and divide the value thru the row number * 100000.

In that case the result is not the same, and you won't get duplicated values.

b1 = a1/(row()*100000) and drag down.  Register To Reply

3. ## Re: Index Match Duplicate Values - ignore cells equal to 0

Also see this link on this forum, for an equal question.

https://www.excelforum.com/excel-for...per-cells.html  Register To Reply

4. ## Re: Index Match Duplicate Values - ignore cells equal to 0

Thanks! Its actually built on that question. The formula there answered my initial question however I need to tweak it to make it ignore 0's  Register To Reply

5. ## Re: Index Match Duplicate Values - ignore cells equal to 0

I appreciate the answer however the above formula takes care of the 0's issue. I now need to manupulate the formula to not include 0's found in the dataset.  Register To Reply

6. ## Re: Index Match Duplicate Values - ignore cells equal to 0

To get help tweaking a formula, you should stay in the same thread.

Welcome to the forum. Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.  Register To Reply