Finding the highest of 3 values, but only headers appear in text string

**DaveBre** · 03-19-2014, 07:41 AM

Hi all,

I have 3 columns (with headers) with values running down a worksheet, and I need to find the highest values in each of the rows (which ordinarily wouldn't be an issue), however I only need to search in as few as 1 of the columns, depending upon which values appear in a text field to the right of the 3. If the heading isn't shown then I can't include it in the comparison.

The text field in Column D will have either (or all of BSS, PBSS or HPRSS) and the formula will need to search through this text field then compare the headers and extract 1-3 values to compare.

I know there has to be a way to achieve it but I can't piece a formula together that works, I've tried Index with Search/Find and Max but to no avail.

As usual any pointers appreciated.

Many Thanks

Max Find.xlsx

**AZ-XL** · 03-19-2014, 07:59 AM

Hi
Paste this array formula to e7 cell and drag doün
=MAX(IF(ISERROR(SEARCH($A$6:$C$6,D7)),0,1)*$A$7:$C$9)

**DaveBre** · 03-19-2014, 04:17 PM

Thanks AZ-XL,

I modified the formula to remove the absolute ref from around the $A:C9 ate the end of the formula, so that it reflected the cells on the following rows B onwards.

The result works well.

Thanks

David

**DaveBre** · 03-19-2014, 04:18 PM

Thanks AZ-XL,

I modified the formula to remove the absolute ref from around the $A:C9 ate the end of the formula, so that it reflected the cells on the following rows B onwards.

The result works well.

Thanks

David

**AZ-XL** · 03-19-2014, 04:21 PM

You are welcome. Thanks for feedback

**DaveBre** · 03-19-2014, 06:09 PM

Hi AZ-XL,

I've just implemented the solution into my working spreadsheet and I've come across an issue.

In my example B9 looks blank, but actually contains a formula, and as a result the formula =MAX(IF(ISERROR(SEARCH($A$6:$C$6,D7)),0,1)*$A$7:$C$9) returns a #Value for that line.

Any idea how I can have the formula ignore the cells in column B which look blank but contain a formula?

Thanks

David

**DaveBre** · 03-19-2014, 06:14 PM

Actually, please ignore. I've substituted "" in column B for 0's, and as a result the formula works.

Thanks

Finding the highest of 3 values, but only headers appear in text string

LinkBack

Thread Tools

Rate This Thread

Display

Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Re: Finding the highest of 3 values, but only headers appear in text string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Finding next 3 highest values after certain criteria

Finding maximum value in a range:highest value in columns

Finding the highest value from two columns of data

Finding the highest values

[SOLVED] Finding (Multiple) Highest Values in Column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions