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

 

[Fluff]

Can you load the XL2BB add-in (see link in my signature) and then use that to post the range in your image?

 

[jasonb75]

It would help if you told us which formula(s) you're trying and what each one is doing incorrectly rather that jut saying the formula is wrong with no indication as to which version you're using.

Last guess, as I noticed an error in the order of the X's when I looked at your last explanation.

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

Dante's formula and the variation that I posted should still be working though.

 

[Bahaa]

It would help if you told us which formula(s) you're trying and what each one is doing incorrectly rather that jut saying the formula is wrong with no indication as to which version you're using.

Last guess, as I noticed an error in the order of the X's when I looked at your last explanation.

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

Dante's formula and the variation that I posted should still be working though.

Jason i am really thankful for your help and follow up to solve the case
SEE i am using this formula
=IF(AND(OR(H5={"Likely","Possible","Unlikely"}),J5="Insignificant"),"X",IF(AND(H5="Unlikely",OR(J5={"Minor","Major"})),"X",""))

But i need you to understand that if i use the formula =IF(AND(OR(H5="Likely","Possible","Unlikely"),AND(J5="Insignificant"),"X","")) !!!! (This is working fine ) but when i add the other part it is not identifying the X . I need to just add that if J5= Minor or Major and H5= Unlikely to turn X in the cell as well

Hope we can solve it

 

[Bahaa]

Jason i am really thankful for your help and follow up to solve the case
SEE i am using this formula
=IF(AND(OR(H5={"Likely","Possible","Unlikely"}),J5="Insignificant"),"X",IF(AND(H5="Unlikely",OR(J5={"Minor","Major"})),"X",""))

But i need you to understand that if i use the formula =IF(AND(OR(H5="Likely","Possible","Unlikely"),AND(J5="Insignificant"),"X","")) !!!! (This is working fine ) but when i add the other part it is not identifying the X . I need to just add that if J5= Minor or Major and H5= Unlikely to turn X in the cell as well

Hope we can solve it

It would help if you told us which formula(s) you're trying and what each one is doing incorrectly rather that jut saying the formula is wrong with no indication as to which version you're using.

Last guess, as I noticed an error in the order of the X's when I looked at your last explanation.

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

Dante's formula and the variation that I posted should still be working though.

Hello Jason
I am using this formula as well and it is working
=IF(AND(OR(H5="Likely",H5="Possible"),AND(J5="Insignificant ")),"X","")
But need to add the other part where if J5= Minor or Major and H5= Unlikely to turn X in the cell as well

 

[jasonb75]

The formulas in posts #4 #15 and #22 will all provide the correct results assuming that we have the criteria correct. After reading your explanations multiple times I'm still not 100% sure that I have it right.

All of the formulas above show "X" for the following combinations in H5 and J5 respectively,
Likely and Insignificant, Unlikely and Major, Unlikely and Minor, Unlikely and Insignificant, Possible and Insignificant

likewise the formulas all show an empty cell for the combinations
Likely and Major, Likely and Minor, Possible and Major, Possible and Minor

If the combinations are not correct then you will need to provide a better explanation of the correct combinations (hint:- keep it simple like I have above, adding if H5 = into your explanations and making it look like a formula is not as helpful as you might think).

If you are getting different results then that is down to a problem other outside of the formula. The only thing that comes to mind which could be a possible cause is manual calculation. Any other issues with the source would result in an "X" not being shown when it should rather than one being shown incorrectly.

 

[DanteAmor]

Hi @Bahaa,
You can upload your file to see exactly your values in the cells.
Please, on the sheet you can put all the possible combinations and in which you want the "X".

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Bahaa]

Hi @Bahaa,
You can upload your file to see exactly your values in the cells.
Please, on the sheet you can put all the possible combinations and in which you want the "X".

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Hello Dante
this is a sharable link on google drive
I inserted comment where combination should match in each cell
Hope you can help ASAP as i got stuck with this

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Many thanks i n advance

 

[Bahaa]

Hi @Bahaa,
You can upload your file to see exactly your values in the cells.
Please, on the sheet you can put all the possible combinations and in which you want the "X".

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

And please consider the second sheet for your review

 

[jasonb75]

There is no file, just a link to a google drive sign in page.

Also, please note the points raised in post 25.

 

[DanteAmor]

Hello Dante
this is a sharable link on google drive
I inserted comment where combination should match in each cell
Hope you can help ASAP as i got stuck with this

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

Many thanks i n advance

The link requests permission to download.
Or share the file in dropbox.