CODEWITHSUNDEEP
excelexcel-formula
Bekir Şahin
Bekir Şahin

Reputation: 5

Excel. How to find the same maximum value when we compare two columns with the same index?

I have the following question. Thank you for your help

Excel two columns, one is for index number, other one is for the values. As it is seen in the figure, index numbers might correspond different values. I want to compare all index numbers The values should be same but maximum If there is no same value leave it blank.

For example, when we compare indices of 1 and 2, the values for index 1 is 6,3,11 and 4. the values for index 2 is 4,2 and 45. So the maximum same number is 4.

I just need a single formula for E3 to copy down.

Thank you for helping.

Figure: enter image description here

Upvotes: 0

Views: 192

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

If one has the dynamic array formula Filter:

=IF(E$2<>$D3,IFERROR(1/(1/MAX(FILTER($B$1:$B$20,$A$1:$A$20=$D3)*(ISNUMBER(MATCH(FILTER($B$1:$B$20,$A$1:$A$20=$D3),FILTER($B$1:$B$20,$A$1:$A$20=E$2),0))))),""),"")

enter image description here

Upvotes: 1

JvdV
JvdV

Reputation: 75930

I assume there won't be any duplicates within the same indice? If not, try the following:

enter image description here

Formula in E3:

=IFERROR(MAX(IF(E$2=$D3,"",FILTERXML("<t><s>"&TEXTJOIN("</s><s>",1,IF($A$1:$A$20=$D3,$B$1:$B$20,IF($A$1:$A$20=E$2,$B$1:$B$20,"")))&"</s></t>","//s[preceding::*=.]"))),"")

The XPATH syntax will only return nodes that have silimar preceding siblings, while MAX() is only included to return the max value when multiple duplciates are encountered between two indices if that's possible.

Note: It's an array entered formula through CtrlShiftEnter

Upvotes: 1

Related Questions