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
|