View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Steve Steve is offline
external usenet poster
 
Posts: 1,814
Default 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