Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
VLookUp  Does the VLookUp return the exact information?  Excel Worksheet Functions  
Vlookup in vlookup  taking the result as array name  Excel Worksheet Functions  
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP  Excel Discussion (Misc queries)  
Vlookup =VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)  New Users to Excel  
Vlookup info being used without vlookup table attached?  Excel Worksheet Functions 