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. 


Thread Tools  Display Modes 
#1




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




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




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 reenter 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




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




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 reenter 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  


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 10: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 15th 06 12:36 AM 
Vlookup =VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)  MikeROz  New Users to Excel  1  March 22nd 06 10:01 AM 
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions  0  January 25th 05 11:43 AM 