Returning the Highest Value in a Range with Text Criteria

[cbrooks]

I am trying to figure out a formula that will return the highest value in a range if the corresponding cell is a certain text.

It looks like this

A B
91 FB
82 CH
79 CH
89 FB
77 CH
83 CH
90 FB

So I named the whole range Pitcher1 and tried this formula “=VLOOKUP("CH", MAX(PITCHER1), 3, FALSE)”, but I am getting N/A back… I have tried IF before VLOOKUP as well and that isn't working...

please help!!!

[DonkeyOte]

One approach

=MAX(IF(B1:B7="CH",A1:A7))
confirmed with CTRL + SHIFT + ENTER

[cbrooks]

I have one to returns the text value for the maximum numeric value
=VLOOKUP(MAX(B:B),B:C,2,FALSE)

however, it's not working when I want to get the max numeric value for the corresponding text value

[rwgrietveld]

equivalent: =MAX(--(B1:B7="CH")*A1:A7) Also an array function

and the NON array function

=SUMPRODUCT(MAX(--(B1:B7="CH")*A1:A7))

[cbrooks]

AWESOME! Thanks for the help and quick reply...

why does CTR+SHFT+ENTER have to make it work?

[rwgrietveld]

I have one to returns the text value for the maximum numeric value
=VLOOKUP(MAX(B:B),B:C,2,FALSE)

however, it's not working when I want to get the max numeric value for the corresponding text value

=MAX(B:B) In both your and my formula the ="CH" criterium is lost ?

[DonkeyOte]

Re: Ctrl + Shift + Enter ... makes the formula an "Array Formula"

http://www.cpearson.com/excel/ArrayFormulas.aspx
http://office.microsoft.com/en-us/ex...872901033.aspx

also see link in my sig. to Colin Legg's extensive tutorial.

@Ricardo... re: SUMPRODUCT... I confess I prefer the IF based approach myself given the same approach would be hard to apply for the MIN equivalent...
(whereas the more traditional Array can simply revert to MIN from MAX by changing the function name)

If you wanted to avoid the Array entry you could embed an INDEX:

=MAX(INDEX(IF($B$1:$B$7="CH",$A$1:$A$7),0))
confirmed with Enter

Over large datasets the embedded INDEX would likely perform slower (so I'm led to believe by those "in the know")

[cbrooks]

sounds good. I've only used array's in a limited amount... but speaking of them, I think I need to write another one...

Using the same sort of data below:

Col A Col B
88 FB
88 FB
89 FB
91 FB
81 CH
81 CH
88 FB

I'd like to be able to count the total number of FB's at 88, 89, etc - and the total number of CH at each number.

these would all go in different cells looking like
total
FB #
3 88

[DonkeyOte]

Given use of XL2007 you should check out the COUNTIFS function in XL Help... it is unfortunate that MS do not offer the same functionality in 2007 for MIN/MAX as they do COUNT, SUM & AVERAGE (hence the need for Arrays).

However, if your intention is to have a table listing the various combinations of Col A & Col B and their frequency you would probably be best served long term using a Pivot Table - more efficient and requires no formula knowledge per se.
For a basic setup you could have Col A as Row Label and Col B as Column Label, add Col B again but as Data Field (should default to Count) .. you can move the fields around to get different layouts.
(for a general intro. to PT's see link in sig. - note you will need column header values for a Pivot Table - eg A1/B1 must be field names)