Vlookup Max of something

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xrbbaker - 16 Sep 2007 14:46 GMT

My lookup value is "house" and the lookup table is below. I want to retun
the MAX value in column 2. How can I do this? I can use vlookup but it just
grabs the first value it comes across. -thx

cabin 2000
cabin 1500
house 2500
house 3000
house 1750

Reply to this Message

Max - 16 Sep 2007 15:04 GMT

Assume data as posted within A1:B5, lookup value entered in D1,
try this in say, E1, array-entered*:
=MAX(IF(A1:A5=D1,B1:B5))
*press CTRL+SHIFT+ENTER to confirm the formula

Adapt the ranges to suit

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> My lookup value is "house" and the lookup table is below. I want to retun
> the MAX value in column 2. How can I do this? I can use vlookup but it just
[quoted text clipped - 5 lines]
> house 3000
> house 1750

Reply to this Message

xrbbaker - 16 Sep 2007 20:34 GMT

Thanks!

> Assume data as posted within A1:B5, lookup value entered in D1,
> try this in say, E1, array-entered*:
[quoted text clipped - 11 lines]
> > house 3000
> > house 1750

Reply to this Message

Max - 17 Sep 2007 02:41 GMT

welcome

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks!

Reply to this Message

Franz Verga - 16 Sep 2007 15:08 GMT

Nel post:365D50F8-AD23-4B26-8896-922D15FD4840@microsoft.com,
xrbbaker <xrbbaker@discussions.microsoft.com> ha scritto:

> My lookup value is "house" and the lookup table is below. I want to
> retun the MAX value in column 2. How can I do this? I can use
[quoted text clipped - 5 lines]
> house 3000
> house 1750

Hi Xrbbaker,

In this case you can use a combination of MAX and SUMPRODUCT function, like
this:

=SUMPRODUCT(MAX(($A$1:$A$5="house")*($B$1:$B$5)))

or, if you use a cell say D5 to write the criteria house:

=SUMPRODUCT(MAX(($A$1:$A$5=D5)*($B$1:$B$5)))

Signature

(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

xrbbaker - 16 Sep 2007 20:34 GMT

thank you

> Nel post:365D50F8-AD23-4B26-8896-922D15FD4840@microsoft.com,
> xrbbaker <xrbbaker@discussions.microsoft.com> ha scritto:
[quoted text clipped - 18 lines]
>
> =SUMPRODUCT(MAX(($A$1:$A$5=D5)*($B$1:$B$5)))

Reply to this Message

Sebation - 16 Sep 2007 15:09 GMT

try:
{=MAX((A1:A5="house")*B1:B5)}
btw:hit the ctrl+shift+enter

Regards
Sebation
"xrbbaker" <xrbbaker@discussions.microsoft.com> Ð´ÈëÏûÏ¢ÐÂÎÅ:365D50F8-AD23-4B26-8896-922D15FD4840@microsoft.com...

> My lookup value is "house" and the lookup table is below. I want to retun
> the MAX value in column 2. How can I do this? I can use vlookup but it
[quoted text clipped - 6 lines]
> house 3000
> house 1750

Reply to this Message

xrbbaker - 16 Sep 2007 20:34 GMT

thanks. beautifully simple. works great

> try:
> {=MAX((A1:A5="house")*B1:B5)}
[quoted text clipped - 13 lines]
> > house 3000
> > house 1750

Reply to this Message