formula to find High or Medium

Appreciate help on this formula Please

Range is B2-L2

Find Very High or High or Medium

Report [from left] first occurrence and put result in M

                                                                                                                                              Result is
Very High        High      Medium      Low      Low      Low         Low      Low      Low      Low      Low                            Very High
Low                Low      Low              Low      Low      Low         Low      Low      Low      Low      Low      
Medium     Low      High              Low      Low      Low         Low      Low      Low      Low      Low                            High
Low                Low      High              Low      Low      Low         Medium Low      Low      Low      Low                            Medium
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


pls try in M2 as array formula (Ctrl-Shift-Enter)

=INDEX({"Very High";"High";"Medium";"Low"},MIN(MATCH((B2:L2),{"Very High";"High";"Medium";"Low"},0)))

Open in new window

[ fanpages ]IT Services ConsultantCommented:
I think your results should be...

Very High
High (not Medium as you stated above)

Please see an alternate approach within the attached workbook.

I have this formula in cell [M2], & copied down to cell [M5]:

The previous response (from Rgonzo1971) does not give you the correct results.  It produces these entries in column [M]:

Very High
High <- Agreeing with what I believe to be the correct value, also.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
Maybe this in M2 down:

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

HI my formula works it s only that I changed the data to test it and forgot to correct it

if you don't want low but nothing then use (edited in case of empty cell)

=INDEX({"Very High";"High";"Medium";"";""},MIN(MATCH((B2:L2),{"Very High";"High";"Medium";"Low";0},0)))

Open in new window

@steve if Very High is further in the cols than Medium your formula won't work
[ fanpages ]IT Services ConsultantCommented:

The data provided does not make this clear, but with your suggestion, a value of "High" or "Medium" in an earlier column [B-L] than "Very High" (in a later column within the same corresponding row) would not produce a result of "Very High", but would return the first matching value.

This may be a desired result, of course, but given the naming of the "priority" values being searched I was presuming "Very High" would take precedence over "High", & that would take precedence over "Medium".

That is why I suggested the value seen in cell [M4] would be "High", whereas your suggestion returns "Medium".

Your return list also differs in the last case:

Very High

I think this question is a good demonstration of how missing one piece of vital information can produce so many suggestions! :)
JagwarmanAuthor Commented:
great solution many thanks
SteveCost AccountantCommented:
I agree FP,
I missed the point considerably... I even first thought the question was after a count of the number of occurences.
In my part this was due to not really getting the structure of the data in the question to work out the requirement. I did not realise that if there was any 'Very High' they should be first, then 'High' ect
The ability to attach workbooks is very useful in EE and somthing which can lend a lot of clarity to a question, as for me it was the structure of the data (after it gets mangled in the question pane) which confused me.

I would always suggest that when asking a question sample data in a small workbook will help obtain an answer faster.

At least the question was anwsered, which is the key thing here.

[ fanpages ]IT Services ConsultantCommented:
"HI my formula works it s only that I changed the data to test it and forgot to correct it".

Sorry, yes, I saw that.  However, "Low" was not a requirement in the results; that cell should have been <blank>.

We shared credit for the solution, & Jagwarman was, I presume, happy with both proposals (sorry, Steve!), so that is the important point to note.

Indeed.  I also struggled with the layout of the question.  Perhaps I will raise a suggestion to try to improve this, especially in data-centric questions that rely on formatting for clarity.  It may be possible to provide a data grid or something of that nature to emulate the presentation of a worksheet for cases like this.  Even just an embedded scrollable area, like the "code" tags provide, using a monospaced/non-proportional font could help.

An example workbook (document, database table, & so on) is often helpful as well.  I have noted that it has become common within the initial responses on many questions that it seems to be a requirement for some "Experts" to participate at all.  Often at the detriment of progression, as by the time the Question Asker returns, the thread may have moved down from the most prominent towards the top of the summary, & it may then be overlooked by those targeting only the first few questions shown.

I appreciate the provision of workbooks can often be problematic due to the presence of personal/sensitive data, & the overall size/complexity of the original file may not lend itself to easily uploading something suitable, especially when multiple worksheets are connected to each other with in-cell formulae.

However, the provision of sample data in a useful format/file is a good indication to show the Asker's commitment to reaching a solution together with the "Experts".

What is worrying is the increasing propensity for a curt response requesting sample data in a physical form that may be taken as a contributor being rude, & this may dissuade the presence of a Question Asker at the site following the conclusion of their question.

Hmm... yes, raising this to the site's staff as a potential source of conflict, & a barrier to prompt responses to questions, does seem like a worthwhile topic of conversation.

I may need to work on the brevity of my point, though :)
JagwarmanAuthor Commented:

my apologies for any confusion my post presented you with and I very much appreciate the solutions you provided. As you said Fanpages, it is not always possible to provide a file because of the sensitivity and sometimes even putting together a dummy file can prove difficult. Having said that, I will in the future, try to include a sample file and also try to make my requests for assistance more clear.
[ fanpages ]IT Services ConsultantCommented:
Thanks Jagwarman :)
SteveCost AccountantCommented:
Indeed, thanks both of you, it is not my intention to 'have a go', I am the one that got the wrong end of the stick (unlike FP).
I think I agree with FP in so far as a simple grid or such for example of data may be handy.
I suppose a screen shot could be embedded, but that can be a painful process too and causes issues having to recreate the data before starting.
It would be an interesting suggestion to see what can be done to improve the use of the question with a 'grid' similar to code such as:
[grid] Column1,Column2,Column3
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.