Result from data in two columns....Best function to use?

  • Thread starter Thread starter tim m
  • Start date Start date
T

tim m

Greetings:

In Column A I will have text data that will be "Significant", "Moderate" or
"Minor"
In Column B I will have text data that will be "Imminent", "Likely",
"Unlikely"

In column C depending on the data in A & B I want to display a result that
which will be "High", "Medium" or "Low"

Significant, Imminent = High
Significant, Likely = High
Moderate, Imminent = High
Significant, Unlikely = Medium
Moderate, Likely = Medium
Minor, Imminent = Medium
Moderate, Unlikely = Low
Minor, Likely = Low
Minor, Unlikely = Low

What function would be the best to use here. One could make a ponderous IF
statement but I'm assuming there is a better way and a better function to use.
 
Hi,

Risk assesments, great fum. I'd build a table like the one below out of the
way which I've put in J1 - M4

Then this formula in B1 and drag down

=INDEX($J$1:$M$4,MATCH(A1,$J$1:$M$1,0),MATCH(B1,$J$1:$J$4,0))

Significant Moderate Minor
Imminent High High Medium
Likely High Medium Low
Unlikely Medium Low Low


Mike
 
tim said:
Greetings:

In Column A I will have text data that will be "Significant", "Moderate" or
"Minor"
In Column B I will have text data that will be "Imminent", "Likely",
"Unlikely"

In column C depending on the data in A & B I want to display a result that
which will be "High", "Medium" or "Low"

Significant, Imminent = High
Significant, Likely = High
Moderate, Imminent = High
Significant, Unlikely = Medium
Moderate, Likely = Medium
Minor, Imminent = Medium
Moderate, Unlikely = Low
Minor, Likely = Low
Minor, Unlikely = Low

What function would be the best to use here. One could make a ponderous IF
statement but I'm assuming there is a better way and a better function to use.
Click to expand...



One possible way:

=CHOOSE(MATCH(A1,{"Minor","Moderate","Significant"},0)
+MATCH(B1,{"Unlikely","Likely","Imminent"},0)
,"Low","Low","Low","Medium","High","High")
 
Set up a table exactly like the one shown, with one column for the
combined comments and another one for High, Medium or Low. Suppose
this occupies L1:M9, like this:

Significant, Imminent High
Significant, Likely High
Moderate, Imminent High
Significant, Unlikely Medium
Moderate, Likely Medium
Minor, Imminent Medium
Moderate, Unlikely Low
Minor, Likely Low
Minor, Unlikely Low

Then in C1 you can have this formula:

=VLOOKUP(A1&", "&B1,L$1:M$9,2,0)

and copy this down as required.

Hope this helps.

Pete
 
Thanks for all the responses.

I used Glenns formula and it worked nicely. (Now I have to reverse engineer
it to learn why it worked.) I think I will use this formula rather than the
other methods because I will be using this on ad hoc type of reports rather
than a more permanent report. thus it will be easier to not create a grid
each time I have to make use of it.

Once again, thanks alot Gents!
 
The MATCH() functions rank the data from one to three as follows:

Minor or Unlikely = 1
Moderate or Likely = 2
Significant or Imminent = 3

The CHOOSE() function selects the correct result. Based upon your data, when
adding those ranks together a total of either 5 or 6 was High, 4 was Medium and
1 through 3 was Low.
 
Back
Top