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.

 Vlookup for a max value ?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Vlookup for a max value ?

#1
July 28th 09, 06:54 PM posted to microsoft.public.excel.worksheet.functions
 Steve external usenet poster 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
 Lars-Åke Aspelin[_2_] external usenet poster 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
 Mike H external usenet poster 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
 Steve external usenet poster 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
 Steve external usenet poster 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

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 08:39 PM.