A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Vlookup for a max value ?



 
 
Thread Tools Display Modes
  #1  
Old July 28th 09, 06:54 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 1,814
Default 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
Ads
  #2  
Old July 28th 09, 07:20 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_2_]
external usenet poster
 
Posts: 913
Default 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  
Old July 28th 09, 07:30 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
Default 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  
Old July 28th 09, 07:42 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 1,814
Default 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  
Old July 28th 09, 07:45 PM posted to microsoft.public.excel.worksheet.functions
Steve
external usenet poster
 
Posts: 1,814
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookUp - Does the VLookUp return the exact information? Cpviv Excel Worksheet Functions 2 October 28th 08 09:57 AM
Vlookup in vlookup - taking the result as array name SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP buffgirl71 Excel Discussion (Misc queries) 12 November 14th 06 11:36 PM
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) MikeR-Oz New Users to Excel 1 March 22nd 06 09:01 AM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 10:43 AM


All times are GMT +1. The time now is 12:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.