Forum Discussion
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?
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.
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?
=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?