# 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 ?

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.

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

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

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1