Forum Discussion

Ideoli Team's avatar
Ideoli Team
Copper Contributor
Apr 02, 2018

MAX/IF and MAXIFS help

I have a large list of product SKUs that are 8 digits total. The SKUs are set up as "XXXX-XXXX". The first set of 4 digits is the product family and the second set of 4 digits is the next spot in the sequence.  I am trying to write a formula that will look up the highest value of each family code in the column.  For example if I have data as follows in a column

 

1101-0001

1101-0002

1101-0003

1102-0001

1102-0002

How do I write a formula to look for return the highest value for the product family starting with 1101, which would give me the value of 1101-0003?

Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Apr 02, 2018

    Hi,

     

    one of many solutions:

    =INDEX(A1:A5,AGGREGATE(14,6,ROW(A1:A5)/(LEFT(A1:A5,4)="1101"),1))
    • Ideoli Team's avatar
      Ideoli Team
      Copper Contributor
      Apr 02, 2018

      Not sure what I am doing wrong but I can't seem to get that formula to work.  It gives me the #DIV/0! error code.  Any other ideas?

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Apr 02, 2018

        The formula cannot return #DIV/0!. It is impossible.

        Another way to solve it:

        {=INDEX(A1:A5,MAX(IF(LEFT(A1:A5,4)="1101",ROW(A1:A5))))}

        Enter with CTRL-SHIFT-ENTER.

Resources