how to return max value with Vlookup

Z

zangel

Hi

I have 2 workbooks. The first one is the order form and the second on
contains the data. In the date file, an item may appear 3-4 times wit
different price.

I did a formula to return a value using vlookup but it only returns m
the first value.

Is there a way to force Excel to return the maximum value?

Thank.

Johann
 
J

Jim Rech

You can do this with an array formula. A simple example:

Your item names are in A1:A10 and the amounts are in B1:B10. The item name
you want to look up is in C1.


=MAX((C1=A1:A10)*B1:B10)

To work this formula must be "array-entered". That means you press
Ctrl-Shift-Enter rather than just Enter.

--
Jim Rech
Excel MVP
| Hi
|
| I have 2 workbooks. The first one is the order form and the second one
| contains the data. In the date file, an item may appear 3-4 times with
| different price.
|
| I did a formula to return a value using vlookup but it only returns me
| the first value.
|
| Is there a way to force Excel to return the maximum value?
|
| Thank.
|
| Johanne
|
|
| ---
|
|
 
Top