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