Forum Discussion

bvelke's avatar
bvelke
Brass Contributor
May 22, 2019

Pivot table lookup?

How can I find the highest value in Column A where the cell in Column B in the same row is not blank?

 

Thanks for any help?

excel
Formulas and Functions

2 Replies

  • bvelke's avatar
    bvelke
    Brass Contributor
    May 23, 2019

    bvelke I think that I can answer my own question now:

     

    {=MAX(IF(ISBLANK(B:B),"",A:A))}

     

    ..where the formula is saved with ctrl+shift+enter because it is an array formula.

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 23, 2019
      These are the alternative non-array formulas:
      =MAX(INDEX(A:A*(B:B<>""),0))
      =SUMPRODUCT(MAX(A:A*(B:B<>"")))
      =AGGREGATE(14,4,A:A*(B:B<>""),1)
      I prefer the last. What about you?