VLOOKUP question

[rmellison]

Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

[Don Guillett]

this is an array formula so must be entered/edited with ctrl+shift+enter

=MAX(IF(B1:B7="abc",C1:C7))

--
Don Guillett
SalesAid Software
[email protected]
"rmellison" <[email protected]> wrote in message
news:[email protected]...
> Hi there Excel Gurus.
>
> I have a long list of data in two columns, and I want to write a formula
> on
> a different sheet that finds the maximum of all values in column 2 that
> correspond to a given character string in column 1. For example.
>
> abc 3
> bcd 5
> cde 7
> abc 2
> def 4
> abc 4
> bcd 8
>
> I need to find the maximum of all values corresponding to abc (ie. 4) or
> to
> bcd (ie. 8) where I can enter the required string in a cell next to my
> formula cell. Have tried filtering but if you use =MAX( ) on the visible
> date
> it still uses the entire range.
>
> Hope you get what I mean. Thanks in advance.

[Peo Sjoblom]

You can use autofilter and filter on abc, then use the formula

=SUBTOTAL(4,B2:B100)

will retun the max value of the filtered item, when in this case the
values are in B2:B100

Regards,

Peo Sjoblom

"rmellison" <[email protected]> wrote in message
news:[email protected]...
> Hi there Excel Gurus.
>
> I have a long list of data in two columns, and I want to write a formula
on
> a different sheet that finds the maximum of all values in column 2 that
> correspond to a given character string in column 1. For example.
>
> abc 3
> bcd 5
> cde 7
> abc 2
> def 4
> abc 4
> bcd 8
>
> I need to find the maximum of all values corresponding to abc (ie. 4) or
to
> bcd (ie. 8) where I can enter the required string in a cell next to my
> formula cell. Have tried filtering but if you use =MAX( ) on the visible
date
> it still uses the entire range.
>
> Hope you get what I mean. Thanks in advance.

[rmellison]

I had tried that previously, array-entered too, but had got #NUM as my error
message. Problem was that I was using the entire column as the reference (B:B
etc) so it was including the header (which is a charater string). Changed the
reference and hey presto! Many thanks for your help.

"Don Guillett" wrote:

> this is an array formula so must be entered/edited with ctrl+shift+enter
>
> =MAX(IF(B1:B7="abc",C1:C7))
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "rmellison" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there Excel Gurus.
> >
> > I have a long list of data in two columns, and I want to write a formula
> > on
> > a different sheet that finds the maximum of all values in column 2 that
> > correspond to a given character string in column 1. For example.
> >
> > abc 3
> > bcd 5
> > cde 7
> > abc 2
> > def 4
> > abc 4
> > bcd 8
> >
> > I need to find the maximum of all values corresponding to abc (ie. 4) or
> > to
> > bcd (ie. 8) where I can enter the required string in a cell next to my
> > formula cell. Have tried filtering but if you use =MAX( ) on the visible
> > date
> > it still uses the entire range.
> >
> > Hope you get what I mean. Thanks in advance.
>
>
>

[rmellison]

This also works well. SUBTOTAL looks like quite a useful function for
filtered lists. Thanks!

"Peo Sjoblom" wrote:

> You can use autofilter and filter on abc, then use the formula
>
> =SUBTOTAL(4,B2:B100)
>
> will retun the max value of the filtered item, when in this case the
> values are in B2:B100
>
> Regards,
>
> Peo Sjoblom
>
> "rmellison" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there Excel Gurus.
> >
> > I have a long list of data in two columns, and I want to write a formula
> on
> > a different sheet that finds the maximum of all values in column 2 that
> > correspond to a given character string in column 1. For example.
> >
> > abc 3
> > bcd 5
> > cde 7
> > abc 2
> > def 4
> > abc 4
> > bcd 8
> >
> > I need to find the maximum of all values corresponding to abc (ie. 4) or
> to
> > bcd (ie. 8) where I can enter the required string in a cell next to my
> > formula cell. Have tried filtering but if you use =MAX( ) on the visible
> date
> > it still uses the entire range.
> >
> > Hope you get what I mean. Thanks in advance.
>
>
>

[Don Guillett]

I should have told you that it didn't work with full columns.

--
Don Guillett
SalesAid Software
[email protected]
"rmellison" <[email protected]> wrote in message
news:[email protected]...
>I had tried that previously, array-entered too, but had got #NUM as my
>error
> message. Problem was that I was using the entire column as the reference
> (B:B
> etc) so it was including the header (which is a charater string). Changed
> the
> reference and hey presto! Many thanks for your help.
>
> "Don Guillett" wrote:
>
>> this is an array formula so must be entered/edited with ctrl+shift+enter
>>
>> =MAX(IF(B1:B7="abc",C1:C7))
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "rmellison" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi there Excel Gurus.
>> >
>> > I have a long list of data in two columns, and I want to write a
>> > formula
>> > on
>> > a different sheet that finds the maximum of all values in column 2 that
>> > correspond to a given character string in column 1. For example.
>> >
>> > abc 3
>> > bcd 5
>> > cde 7
>> > abc 2
>> > def 4
>> > abc 4
>> > bcd 8
>> >
>> > I need to find the maximum of all values corresponding to abc (ie. 4)
>> > or
>> > to
>> > bcd (ie. 8) where I can enter the required string in a cell next to my
>> > formula cell. Have tried filtering but if you use =MAX( ) on the
>> > visible
>> > date
>> > it still uses the entire range.
>> >
>> > Hope you get what I mean. Thanks in advance.
>>
>>
>>