IF conditional Help

[Bahaa]

I have rows and columns and need to insert the below in concept in formula

Columns are Major Minor and Insignificant
Rows are LIkely possible and Unlikely

=IF(AND(OR(H5="Likely",H5="Possible", H5="Unlikely"),AND(J5="Insignificant","Minor","Major ")),"X","")

If columns with a certain selection were equal to likely or possible or unlikely and at the same time rows were equal to insignificant or minor or major then insert x

Thanks in advance for all replies

 

[Bahaa]

I think I have not understood.
But according to your last request, you need this:

=IF(AND(OR(H5={"Likely","Possible","Unlikely"}),J5="Insignificant"),"X",IF(AND(H5="Unlikely",OR(J5={"Minor","Major"})),"X",""))

If not, you could write all the combinations you need to validate.

Hello Dante
Please i need your help
IF(AND(OR(H5="Likely",H5="Possible",H5="Unlikely"),AND(J5="Insignificant ")),"X","") **** Here the formula is fine enough and it is working properly but when i need to add the combination of &IF(AND(OR(H5="Unlikely"),AND(J5="Minor","Major")),"X","") it is not working

If at the same time all the following matches
H5= Likely or Possible or Unlikely and J5= Insignificant and if H5= Unlikely and J5= Minor and Major , then X should appear in the cell

 

[jasonb75]

Try this version of the formula that I suggested.

=INDEX({"","","X";"","","X";"X","X","X"},MATCH(H5,{"Likely","Possible","Unlikely"},0),MATCH(J5,{"Major","Minor","Insignificant"},0))

The X's form a matrix as below, with the first 3 parts of the array making up the top row of the matrix, the next 3 the second row, the last 3 the third row.

If the formula returns an empty cell where there should be an X then you need to put an X between the correct pair of double quotes. If the formula gives you an X when it should be blank then you need to remove the X (but leave the double quotes for the blank cell.

	Major	Minor	Insignificant
Likely			X
Unlikely			X
Possible	X	X	X

It would be more sensible to have a matrix like the one above in your sheet, then refer to the table with an INDEX MATCH formula.

 

[Bahaa]

Hello Jason
I do not want to make the table that you have drawn as i have automatic Change with the criterias in cells
I have columns G H I J
If i do some changes in column G and I based on certain criterias i have defined then automatically H and J changes where i need the following below combinations if you can help

At the same time and if all the following matches
H5= Likely or Possible or Unlikely and J5= Insignificant and if H5= Unlikely and J5= Minor or Major , then X should appear in the cell

Thanks for your prompt response and help

 

[jasonb75]

The formula in Post #12 will do exactly that, but it could show a #N/A error if H5 or J5 show anything not in the list, an error trap will resolve that.

=IFERROR(INDEX({"","","X";"","","X";"X","X","X"},MATCH(H5,{"Likely","Possible","Unlikely"},0),MATCH(J5,{"Major","Minor","Insignificant"},0)),"")

The table will not make any difference to what you have already, so that is no excuse for not trying it. I'm trying to make it as simple as possible for you to follow.

Make a table like the one shown above in A1:D4 of a new sheet, call the sheet Matrix (do not copy and paste the one above, hidden characters will cause problems).

In K5, enter the formula

=IFERROR(INDEX(Matrix!$B$2:$BD$4,MATCH(H5,Matrix!$A$2:$A$4,0),MATCH(J5,$B$1:$D$1,0)),"")

Any changes you make to the matrix will update without needing to edit the formulas.

 

[jasonb75]

Looking back, Dante's formula should have worked, as should this variation of it, along with my last 2 suggestions.

=IF(OR(AND(OR(H5={"Likely","Possible","Unlikely"}),J5="Insignificant"),AND(H5="Unlikely",OR(J5={"Minor","Major"}))),"X","")

If none of the suggestions work then the problem is in your data, not the formula.
As an example, if H5 contains "Minor " instead of "Minor" then you will not get an X from any of the formulas.

 

[Bahaa]

Looking back, Dante's formula should have worked, as should this variation of it, along with my last 2 suggestions.

=IF(OR(AND(OR(H5={"Likely","Possible","Unlikely"}),J5="Insignificant"),AND(H5="Unlikely",OR(J5={"Minor","Major"}))),"X","")

If none of the suggestions work then the problem is in your data, not the formula.
As an example, if H5 contains "Minor " instead of "Minor" then you will not get an X from any of the formulas.

No see Jason
H5 could be equal to likely or possible or unlikely
J5 could be equal to Minor or Major or Insignificant

if H5 = Likely and J5 = Insignificant then X should appear
If H5= Unlikely and J5 = Insignificant then X should appear
if H5 = Possible and J5 Insignificant then X should appear

on the other hand i need to make sure as well
If J5= Minor and H5= Unlikely then X should appear
If J5= Major and H5 = Unlikely then X should appear

Hope u get my point and you can help

 

[Fluff]

That's exactly what the formulae do.
What formulae do you have in H5 & J5?

 

[jasonb75]

Are you sure that it should be H5 and J5?

The screen capture in an earlier post appears to contain merged cells which could be a big part of the problem.

 

[Bahaa]

H

That's exactly what the formulae do.
What formulae do you have in H5 & J5?

Hello Fluff
In H5 i have this formula =IFERROR(IF(ISNUMBER(FIND($G5,"A B C ",1)),VLOOKUP(TRIM($G5),'Risk Matrix'!$A$4:$B$6,2,FALSE),""),"") which gets data from other sheet where when i insert A or B or C in G cell then data matches other sheet and get the Likely and Possible and unlikely

In J5 i have =IFERROR(IF(ISNUMBER(FIND($I5,"1 2 3 ",1)),VLOOKUP(I5,'Risk Matrix'!$A$11:$B$13,2,FALSE),""),"")
which gets data from other sheet where when i insert 1 or 2 or 3 in I cell then data matches other sheet and get the Minor and Major and Insignificant

 

[Bahaa]

Are you sure that it should be H5 and J5?

The screen capture in an earlier post appears to contain merged cells which could be a big part of the problem.

No Jason
I have other formulas and it is working properly, still have this combination but unfortunately it is not working