Let Excel recommend the largest current value among a series of data? Please help!!!

ashtar123 

=MID(INDEX($H$2:$H$11,LARGE(IF((LEFT($H$2:$H$11,3)=A2)*(NUMBERVALUE(MID($H$2:$H$11,4,4))=B2)*(MID($H$2:$H$11,8,3)=C2),ROW($H$2:$H$11)-1),1)),11,3)

Another solution could be this formula in cell D2. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=RIGHT(INDEX($H$2:$H$11,LARGE(IF((LEFT($H$2:$H$11,3)=A2)*(NUMBERVALUE(MID($H$2:$H$11,4,4))=B2)*(MID($H$2:$H$11,8,3)=C2),ROW($H$2:$H$11)-1),1)),3)

This formula is in cell E2. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

ashtar123 

How to make a meal of it in 365!

The formula goes in cell A2 and spills to form the result table.

= LargestCodesλ(uniqueID)

The Lambda function is defined to be

= LET(
      productCode,  LEFT(uniqueID, 10),
      locationCode, VALUE(RIGHT(uniqueID, 6)),
      distinct,     UNIQUE(productCode),
      finalCode,    MAP(distinct, LAMBDA(d,
          LET(
              filteredLocation, FILTER(locationCode, productCode = d),
              d & TEXT(MAX(filteredLocation), "000000")
          )
      )),
      MID(finalCode, {1, 4, 8, 11, 14}, {3, 4, 3, 3, 3})
  )

[Lists distinct products with their largest Aisle and Tray codes]

ashtar123 

Hans formula is perfect if you always search for the last entry. If the largest entry is not the last one, it does not work. You could use my solution for this.

D2=

=LET(in,FILTER(H$2:H$11,ISNUMBER(FIND(A2&B2&C2,H$2:H$11))),TEXT(MAX(VALUE(LEFT(TEXTAFTER(in,C2),3))),"000"))

 E2=

=LET(in,FILTER(H$2:H$11,ISNUMBER(FIND(A2&B2&C2&D2,H$2:H$11))),TEXT(MAX(VALUE(RIGHT(in,3))),"000"))

ashtar123 

In D2:

=LEFT(RIGHT(LOOKUP(A2&B2&C2&REPT("z",6),H:H),6),3)

In E2:

=RIGHT(LOOKUP(A2&B2&C2&REPT("z",6),H:H),3)

Fill down  from D2:E2 to D3:E3.