Returning Max Value Using Vlookup with Multiple Results

Question

Trying to work on a VLOOKUP formula for a calculator that I am working on that returns the highest value from column B where columns A = True. Unfortunately what I am getting with my current attempts is returning 1 not 9 like expected and hoping for some assistance as to what I am doing wrong/if what I want to do is even achievable.

Formula is =VLOOKUP("True", A1:B6,MAX(2),FALSE)

	A	B
. 1	False	2
2	False	5
3	True	1
4	False	9
5	True	9
6	True.	8
7	False	10

Thanking you in advance for your time.

Sadly I am limited by the functions I can use/macros can't be used in this instance

If your version is as old as 2010 then you can use =AGGREGATE(14,4,B2:B8*C2:C8,1) — Spectral Instance
– Spectral Instance, Commented Mar 26 at 21:55

saskiaclr · Accepted Answer · 2025-03-26 09:55:27Z

2

You can use this function:

=MAX(FILTER($B$1:$B$7, $A$1:$A$7=TRUE))

The filter here will essentially remove any of the values where column A=False

answered Mar 26 at 9:55

saskiaclr

611 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Sharpe Mar 26 at 10:27

This will correctly return an error message if no rows satisfy the criteria.

saskiaclr Mar 26 at 10:32

@TomSharpe true, and you can simply add =IFERROR(MAX(FILTER($B$1:$B$7, $A$1:$A$7=TRUE)), "") if you would rather the cell stay blank, or another comment like "no matching criteria"

Tom Sharpe · Accepted Answer · 2025-03-26 10:26:00Z

2

If your version of Excel is fairly recent (2019 or later) you can just use the Maxifs function

=MAXIFS(B1:B7,A1:A7,TRUE)

Having said that, it has to be used with care because if there are no values matching the criteria in column A, it will silently return zero, as does the Max(if... solution. Therefore the Filter solution (which does give a #CALC! error) is arguably better and I will upvote it.

edited Mar 26 at 10:26

answered Mar 26 at 10:12

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Collectives™ on Stack Overflow

Returning Max Value Using Vlookup with Multiple Results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related