find longest string in with criteria - excel

Question

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

Answer 1

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

1. Turn your source data into an Excel Table

2. 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:

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

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

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

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

BING!

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

Answer 2

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))