How can I display the max text value from a range

[Didas007]

I have a data sheet that contains ratings for about 100 course leaders at my company. There are 56 date columns in ascending order from left to right going back 7 years (we conduct 8 courses a year), If an employee led that particular course, they are assigned one of 6 ratings and it's listed in the cell, if they did not lead the cell is blank.

I need a formula that looks across this range and displays the highest rating achieved. To date I have only been able to find "max" formulas that relate to numeric values. I do not want to replace each rating with a numeric value directly in the data, because I use pivot tables for other reasons on that data and need to have it displayed as is.

The ratings are:
1. Pre-rankings Leader
2. New Leader
3. Leader
4. Reliable Leader
5. Team Leader
6. Anchor

Please help!

 

[yogeshmaney]

plz provide data sample

 

[schielrn]

Welcome to the Board!

If your ratings are in say A2:A7, if you use:

=match(cellforLookup,A2:A7,0)

That will return 1 for Pre-rankings Leader, 2 for New Leader and so on. Is this sort of what you are wanting?

I'm not completely sure I am following what you are asking.

You could also try to post some sample data and expected results.

 

[Didas007]

Hi schielrn, thanks for the greetings and quick response!

Your solution isn't quite what I need because the MATCH formula requires a single cell reference, whereas I have a range of cells. I want the formula to look at a large range of cells in a single row containing various text, with many cells within the range blank, and display the highest value from within that range. The highest value is based upon a list I include in the excel sheet.

The forum here doesn't enable me to post attachments, but i tried to make a basic outline below of what I need. Underneath the "Expected Result" is where I want to put in a formula. Please advise and thanks again!


Feb-10 Apr-10 Jul-10 Sep-10 "Expected Result"
Anchor Leader blank Anchor "Anchor"
Team Ldr blank Rel. Ldr Leader "Team Ldr"
New Ldr blank blank Leader "Ldr"
blank blank blank blank "Has not led"


Rank Value Leader Type
1 Pre-rankings Leader
2 New Leader
3 Leader
4 Reliable Leader
5 Team Leader
6 Anchor

 

[yogeshmaney]

http://rapidshare.com/files/440978247/Mrexcel.xlsx

formula in yellow cell.. do the row column alterations

 

[schielrn]

This is one way, the blanks make it a little harder because there is not a match for blanks in your table, but if it is blank it pre-fills with the lowest rank in H2, which is not a problem per se.

Really the rank values are not used at all since you have them in ascending order anyways.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Feb-10	Apr-10	Jul-10	Sep-10	Result		Rank Value	Leader Type
2	Anchor	Leader		Anchor	Anchor		1	Pre-rankings Leader
3	Team Leader		Reliable Leader	Leader	Team Leader		2	New Leader
4	New Leader			Leader	Leader		3	Leader
5					Has not led		4	Reliable Leader
6							5	Team Leader
7							6	Anchor
Sheet1

#VALUE!

Hope that helps and post back with any questions or comments.

 

[Didas007]

It's still not quite working, I'm getting a #VALUE error. I copied it all the way down and the error appears in each cell. When I first input the formulas you provided a message pops up saying there is an error and tries to correct MATCH(IF(A3:D3 < > "" to be MATCH(IF(A3:D3<"" - it's removing the greater than symbol and removing the spaces in this section.

I'm not familiar with the INDEX function to i'm not sure how to troubleshoot the formula. Can you please advise?

If the entirely blank rows add complexity, it's ok to have a formula that causes these rows to result in an error value, I'll just add another column with an =IFERROR function to replace them with "has not led."

Lastly, I'm not sure what "entered with Ctrl+Shift+Enter means, is this something I should be doing?

Thanks again!

 

[schielrn]

It because the html maker added some spaces in there, it should be:

=IF(COUNTBLANK(A5:D5)=COLUMNS(A5:D5),"Has not led",INDEX($H$2:$H$7,MAX(MATCH(IF(A5:D5<>"",A5:D5,H5),$H$2:$H$7,0))))

Also for control+shift+enter, you need to confirm it with that and not just enter because it is an array formula, you may also be able to use this:

=IFERROR(INDEX($H$2:$H$7,MAX(MATCH(IF(A5:D5<>"",A5:D5,H5),$H$2:$H$7,0))),"Has not led")

Hope that helps.