Vlookup for a max value ?

Working with 2 tabs.
Tab B! has the name Smith in cell K3
Tab A! has 20 Smiths, 15 Jones', 16 Millers, etc. Multi names(various
counts) in row F. Row Q has various values. In cell L3. next to Smith in Tab
B!, I'd like the max value of Q corresponding to Smith. What formula would I
enter in L3 ?

Tab A!
F Q
Smith 1
Smith 3
Smith 24
Jones 4
Jones 17
Jones 10
etc


Tab B!
K L
Smith 24
Jones 17
etc.

I hope this is understandable ?

Thanks,

Steve

On Tue, 28 Jul 2009 10:54:01 -0700, Steve
> wrote:

>Working with 2 tabs.
>Tab B! has the name Smith in cell K3
>Tab A! has 20 Smiths, 15 Jones', 16 Millers, etc. Multi names(various
>counts) in row F. Row Q has various values. In cell L3. next to Smith in Tab
>B!, I'd like the max value of Q corresponding to Smith. What formula would I
>enter in L3 ?
>
>Tab A!
> F Q
>Smith 1
>Smith 3
>Smith 24
>Jones 4
>Jones 17
>Jones 10
>etc
>
>
>Tab B!
> K L
>Smith 24
>Jones 17
>etc.
>
>I hope this is understandable ?
>
>Thanks,
>
>Steve

Try this formula in cell L3:

=MAX(IF('Tab A'!F$1:F$100=K3,'Tab A'!Q$1:Q$100))

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Replace 100 in two places to fit the size of your data table in Tab A.

Copy the formula down in column L as far as needed.

Hope this helps / Lars-Åke

Steve,

For the avoidance of doubt we need to get some terminology correct. You are
working with 2 'worksheets' and not 2 TABS. Tabs are part of a worksheet that
contain the name and provide some other functionality.

So on Sheet1 you have your data in columns F & Q and on another sheet you
have the same list of names in Column K. Try this 'Array' formula in column
L. Enter as an array (see below) and drag down

=MAX(IF(Sheet1!$F$1:$F$6=K1,Sheet1!$Q$1:$Q$6))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array.

Mike

"Steve" wrote:

> Working with 2 tabs.
> Tab B! has the name Smith in cell K3
> Tab A! has 20 Smiths, 15 Jones', 16 Millers, etc. Multi names(various
> counts) in row F. Row Q has various values. In cell L3. next to Smith in Tab
> B!, I'd like the max value of Q corresponding to Smith. What formula would I
> enter in L3 ?
>
> Tab A!
> F Q
> Smith 1
> Smith 3
> Smith 24
> Jones 4
> Jones 17
> Jones 10
> etc
>
>
> Tab B!
> K L
> Smith 24
> Jones 17
> etc.
>
> I hope this is understandable ?
>
> Thanks,
>
> Steve

Perfect.

Thanks much,

Steve

"Lars-Ã…ke Aspelin" wrote:

> On Tue, 28 Jul 2009 10:54:01 -0700, Steve
> > wrote:
>
> >Working with 2 tabs.
> >Tab B! has the name Smith in cell K3
> >Tab A! has 20 Smiths, 15 Jones', 16 Millers, etc. Multi names(various
> >counts) in row F. Row Q has various values. In cell L3. next to Smith in Tab
> >B!, I'd like the max value of Q corresponding to Smith. What formula would I
> >enter in L3 ?
> >
> >Tab A!
> > F Q
> >Smith 1
> >Smith 3
> >Smith 24
> >Jones 4
> >Jones 17
> >Jones 10
> >etc
> >
> >
> >Tab B!
> > K L
> >Smith 24
> >Jones 17
> >etc.
> >
> >I hope this is understandable ?
> >
> >Thanks,
> >
> >Steve
>
> Try this formula in cell L3:
>
> =MAX(IF('Tab A'!F$1:F$100=K3,'Tab A'!Q$1:Q$100))
>
> Note: This is an array formula that has to be confirmed with
> CTRL+SHIFT+ENTER rather than just ENTER.
>
> Replace 100 in two places to fit the size of your data table in Tab A.
>
> Copy the formula down in column L as far as needed.
>
> Hope this helps / Lars-Ã…ke
>
>

Yes, two tabs/sheets in the same WB.

Thanks,


"Mike H" wrote:

> Steve,
>
> For the avoidance of doubt we need to get some terminology correct. You are
> working with 2 'worksheets' and not 2 TABS. Tabs are part of a worksheet that
> contain the name and provide some other functionality.
>
> So on Sheet1 you have your data in columns F & Q and on another sheet you
> have the same list of names in Column K. Try this 'Array' formula in column
> L. Enter as an array (see below) and drag down
>
> =MAX(IF(Sheet1!$F$1:$F$6=K1,Sheet1!$Q$1:$Q$6))
>
> This is an array formula which must be entered with CTRL+Shift+Enter and NOT
> 'just enter. If you do it correctly then Excel will put curly brackets around
> 'the formula{}. You can't type these yourself. If you Edit the ranges
> 'then you must re-enter as An array.
>
> Mike
>
> "Steve" wrote:
>
> > Working with 2 tabs.
> > Tab B! has the name Smith in cell K3
> > Tab A! has 20 Smiths, 15 Jones', 16 Millers, etc. Multi names(various
> > counts) in row F. Row Q has various values. In cell L3. next to Smith in Tab
> > B!, I'd like the max value of Q corresponding to Smith. What formula would I
> > enter in L3 ?
> >
> > Tab A!
> > F Q
> > Smith 1
> > Smith 3
> > Smith 24
> > Jones 4
> > Jones 17
> > Jones 10
> > etc
> >
> >
> > Tab B!
> > K L
> > Smith 24
> > Jones 17
> > etc.
> >
> > I hope this is understandable ?
> >
> > Thanks,
> >
> > Steve