Excel技术问询：如何按Item列查找对应最长长度的ID内容

Hey Brian, your initial idea of using INDEX+MATCH was close—we just need to adjust the formula to prioritize the longest ID length for each Item. Let’s break this down for both older Excel versions (that require array entry) and modern dynamic array Excel (365/2021):

1. For Older Excel Versions (Pre-365/2021) #

Use this array formula (must enter with Ctrl+Shift+Enter, not just Enter):

=INDEX($B$2:$B$100, MATCH(MAX(LEN($B$2:$B$100)*($A$2:$A$100=A2)), LEN($B$2:$B$100)*($A$2:$A$100=A2), 0))

How it works: #

• LEN($B$2:$B$100)*($A$2:$A$100=A2): Creates an array where only IDs matching the current Item in A2 keep their length value—all others become 0.
• MAX(...): Grabs the highest length value from that filtered array (the longest ID for the Item).
• MATCH(...): Finds the first position where that maximum length occurs in the filtered array.
• INDEX(...): Pulls the corresponding ID from column B.

Pro tips:

• Replace $B$2:$B$100 and $A$2:$A$100 with your actual data range (avoid full column references like A:A for better performance).
• Add IFERROR to handle empty Items/IDs: =IFERROR(INDEX(...), "")

2. For Modern Excel (365/2021 with Dynamic Arrays) #

No need for array entry—these formulas work with a regular Enter press, and can auto-fill results for all Items at once:

Single cell formula (for one Item at a time): #

=XLOOKUP(MAX(LEN($B$2:$B$100)*($A$2:$A$100=A2)), LEN($B$2:$B$100)*($A$2:$A$100=A2), $B$2:$B$100)

Bulk formula (auto-generate results for all Items): #

Enter this in cell C2, and it will spill results down to match your Item list:

=BYROW($A$2:$A$100, LAMBDA(item, XLOOKUP(MAX(LEN($B$2:$B$100)*($A$2:$A$100=item)), LEN($B$2:$B$100)*($A$2:$A$100=item), $B$2:$B$100)))

Edge case note: #

If multiple IDs for the same Item have the same maximum length, both formulas will return the first occurrence of that longest ID. If you need to return all matching longest IDs, you can modify the formula with TEXTJOIN to concatenate them:

=TEXTJOIN(", ", TRUE, IF((LEN($B$2:$B$100)=MAX(LEN($B$2:$B$100)*($A$2:$A$100=A2)))*($A$2:$A$100=A2), $B$2:$B$100, ""))

(For older Excel, enter this as an array formula; for modern Excel, it works with Enter.)

内容的提问来源于stack exchange，提问作者Brian Atakhodjaev