Remember Me?

#1
July 28th 09, 06:54 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,814
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

#2
July 28th 09, 07:20 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2008 Posts: 913
Vlookup for a max value ?

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

#3
July 28th 09, 07:30 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
Vlookup for a max value ?

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

#4
July 28th 09, 07:42 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,814
Vlookup for a max value ?

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

#5
July 28th 09, 07:45 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,814
Vlookup for a max value ?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Cpviv Excel Worksheet Functions 2 October 28th 08 10:57 AM SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM buffgirl71 Excel Discussion (Misc queries) 12 November 15th 06 12:36 AM MikeR-Oz New Users to Excel 1 March 22nd 06 10:01 AM Excel Worksheet Functions 0 January 25th 05 11:43 AM

All times are GMT +1. The time now is 11:18 PM.