find longest string in with criteria - excel

3
votes

I have two columns Item and ID - refer to the Sample Table. I need a formula in Excel to determine the longest (based on simple length of ID) per item.

Here is what I am trying to extract from the sample table:

Item 1 ABCDABC
Item 2 XXXYZ

I tried index match combination: 

=INDEX("ID column",MATCH(MAX(LEN("ID column")),LEN("ID column"),0),MATCH("Item 1" , "Item Column",0))

with ctrl+shift+enter

here are the results:

Item 1 ABCDABC
Item 2 #REF!

Any help will be greatly appreciated.

Sample Table
Item        ID 
Item 1      ABC
Item 1      ABCD
Item 1      ABCDA
Item 1      ABCDAB
Item 1      ABCDABC
Item 2      X
Item 2      XX
Item 2      XXX
Item 2      XXXY
Item 2      XXXYZ
2
excelexcel-formula

2 Answers

3
votes

This will return the correct index:

=INDEX($B$2:$B$11,MATCH(MAX(INDEX(LEN($B$2:$B$11)*($A$2:$A$11=D3),)),INDEX(LEN($B$2:$B$11)*($A$2:$A$11=D3),),0))

enter image description here

2
votes

A PivotTable is perfect for this, because it will handle ties.

  1. Turn your source data into an Excel Table

enter image description here

  1. Add a new column to your source data, and use the LEN function to return the length of each item. You only need to add the formula to the top row, and Excel will copy it down using Table notation:

enter image description here

  1. Make a PivotTable out of that Table, and put Item and ID in the ROWS pane and put Length into the Values pane:

enter image description here

  1. Right-click on one of the cells in the Sum of Length PivotField, and change the aggregation to MAX:

enter image description here

  1. Click the ID filter dropdown, and select Values Filter > Top 10...

enter image description here

  1. In the dialog box, change the 10 in the to a 1 and click OK:

enter image description here

BING!

enter image description here

...and if two IDs within the same category have the exact same length, the PivotTable shows you both of them:

enter image description here