It isn't clear what you are asking for. By largest value with multiple
matches, do you mean to return the item that is in the highest
numbered row? Or do you mean the largest value from the lookup? For
example, suppose you have in C4:C20 and D4:D20,

1 100
2 200
3 900
1 400
2 500
3 600
4 700


If you are looking up the value 3 in C4:C20, which instance of 3 do
you want to return? The first lookup match for 3 is 900 and the last
row match for 3 is 600. Which you do want? To get the value in D for
the last occurrence of 3 in C, use the following array formula:

=OFFSET(C$4,MAX((C4:C20=3)*(ROW(C4:C20)))-ROW(C$4),1,1,1)

To get the max value in D where C = 3, use

=MAX((C4:C20=3)*(D4:D20))

This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







On Wed, 20 Jan 2010 12:56:01 -0800, Shaun
wrote:

How would I add logic to this formula =VLOOKUP($A2,EXP,6,FALSE) so that it
returns the largest value if I have multiple matches in the table?