Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
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 |