Home

Pivot table lookup?

%3CLINGO-SUB%20id%3D%22lingo-sub-636262%22%20slang%3D%22en-US%22%3EPivot%20table%20lookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-636262%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20I%20find%20the%20highest%20value%20in%20Column%20A%20where%20the%20cell%20in%20Column%20B%20in%20the%20same%20row%20is%20not%20blank%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-636262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642796%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20lookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642796%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347015%22%20target%3D%22_blank%22%3E%40bvelke%3C%2FA%3E%26nbsp%3BI%20think%20that%20I%20can%20answer%20my%20own%20question%20now%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DMAX(IF(ISBLANK(B%3AB)%2C%22%22%2CA%3AA))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E..where%20the%20formula%20is%20saved%20with%20ctrl%2Bshift%2Benter%20because%20it%20is%20an%20array%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-642857%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20lookup%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-642857%22%20slang%3D%22en-US%22%3EThese%20are%20the%20alternative%20non-array%20formulas%3A%3CBR%20%2F%3E%3DMAX(INDEX(A%3AA*(B%3AB%26lt%3B%26gt%3B%22%22)%2C0))%3CBR%20%2F%3E%3DSUMPRODUCT(MAX(A%3AA*(B%3AB%26lt%3B%26gt%3B%22%22)))%3CBR%20%2F%3E%3DAGGREGATE(14%2C4%2CA%3AA*(B%3AB%26lt%3B%26gt%3B%22%22)%2C1)%3CBR%20%2F%3EI%20prefer%20the%20last.%20What%20about%20you%3F%3C%2FLINGO-BODY%3E
bvelke
Occasional Contributor

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?

2 Replies

@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?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies