Vlookup Max of something

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Nel post:[email protected],
xrbbaker said:
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
Click to expand...

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)))



--
(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
 
Thanks!

Max said:
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
Click to expand...
 
thank you

Franz Verga said:
Nel post:[email protected],


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)))



--
(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
Click to expand...
 
Back
Top