Vlookup for a max value ?

[Steve]

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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]

Perfect.

Thanks much,

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]

Yes, two tabs/sheets in the same WB.

Thanks,

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