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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com