CODEWITHSUNDEEP
excelexcel-formulaindex-match
user17392778
user17392778

Reputation:

Find Same Duplicate Max Value Excel

So i try return value from table which has same percentage using Index match, but it return the same value twice instead of another value that has the same percentage Here is my Excel View, is there any help for my problem?

Here is my excel formula

enter image description here

Upvotes: 0

Views: 1006

Answers (1)

Apostolos55
Apostolos55

Reputation: 580

1st: you can't and shouldn't use Match(...,...,-1) in unsorted data, use Match(...,...,0) only

2nd: you can't search for the 2nd similar because... the program has no way of knowing there are 2|3... similar objects. So you must get the positions of ranks when sorting them, and then use that position to get the score and value you want with index(). So Sorting and getting the Position (or ROW()) must be done same time.

3rd: the way to do this is with Matrix formulas that work for on a pre-specified number of cells: So you can look in a table of 200 elements for 10 results and have provision to avoid errors if results are <10, but you'll never get 11. Also Hide the column with the positions to gain clarity...

try this formula:

= LARGE( ROUND( OFFSET($B$3,,,$G$1)*1000,0)*100 + ROW(OFFSET($A$1,,,$G$1)),ROW(OFFSET($A$1,,,$G$2)) )

to get a sorted list from column B Starting w B3 with N rows (N specified in G1) and look for m results (m specified in g2). Offcourse adjust the bolds as needed for your project

4th: Enter the formula in a Column that you will hide, with ctrl+shft+Enter (to enter as matrix) and expand it in as many cells as the results you expect (in the example 2) by selecting them starting with the cell with Array-formula, then F2 (to edit formula) and ctrl+sht+enter. Mind that Arrays can only be EXPANDED, if you want to shrink it you have to delete it and rebuild it...

5th: get the Index with =MOD( ... ,100) ,where ... are the results from steps 3&4

1ST AGAIN: Another simple solution (*not to my Liking). Within your Match(item,range,-1) you should compare item with previous item and use Offset. In your formula you can write: =Index($C$12:$C$23, Match(DF19, Offset( $CQ$12:$CQ$23 ,if(DF19=DF18, Match(DF19,$CQ$12:$CQ$23,0),0) ,-1) )) thus pushing the search matrix further down to avoid finding the same. (added code with BOLD, rest should be same to your original code)

Upvotes: 0

Related Questions