If/And and Wildcards

Help!

I'm trying to create a formula so that if the S&P rating contains "A1" AND the Moody's rating contains "P1", then "High grade" would be returned; if it contains "A2" AND contains "P2", then "Medium grade" is returned, otherwise return "N/A". Can someone help? I need to use wildcards because the rating could be A1, A1+ or A1- so I wanted to use wildcards to pick up anything with "A1".

S&P Rating Moody's Rating Rating
A1+ P1

Hi Pepe, thanks for the suggestion, I've spent hours trying to figure this out! This does the trick except if the ratings are split (for instance A1 and P2), it returns N/A. Is there a way to get it to return a value based on the lower rating, so in this case "Medium Grade"? I attached the workbook using your formula.

Thanks again!!

Originally Posted by Jakobshavn

Assuming S&P is in A1 and Moody's is in B1:

=IF(AND(LEFT(A1,2)="A1",LEFT(B1,2)="P1"),"HighGrade",IF(AND(LEFT(A1,2)="A2",LEFT(B1,2)="P2"),"Medium Grade","N/A"))

Thanks! this looks like another alternative that works except for the issue I posted to Pepe about if the ratings are split (S&P A1, Moody's P2) is there a way to return the lower value (in this case "Medium Grade") instead of returning N/A?

Book2.xlsx

Originally Posted by K m

=IF(AND(LEFT(A1,2)="A1",B1="P1"),"High grade",IF(OR(AND(LEFT(A1,2)="A1",B1="P2"),AND(LEFT(A1,2)="A2",B1="P2")),"Medium grade","N/A"))

Hi K m, thanks for helping me with this! This is working for split A1,P2, but not for split A2,P1. Also for A1/P1- it comes up as N/A (which I think has to do with part of the original IF(AND) statement. I tried following your method to correct it, but am getting a formula error and can't figure out where it's from. I attached the workbook with the formula I was trying, can you take a look?

Originally Posted by K m

You did not state the problem correctly the first time. You did not indicate that P1 or P2 could also have a + or - after it, ie P1- or P1+

Try this for cell C16:

=IF(AND(LEFT(A16,2)="A1",LEFT(B16,2)="P1"),"High grade",IF(OR(AND(LEFT(A16,2)="A1",LEFT(B16,2)="P2"),AND(LEFT(A16,2)="A2",LEFT(B16,2)="P2")),"Medium grade","N/A"))


A1 and P1 returns a "High grade"

I apologize if I wasn't clear earlier, the various combos were the reason I thought I had to use wildcards...in any case I made a slight tweak to your proposed formula b/c it still wasn't picking up split A2/P1 combinations that should result in Medium Grade and now it works perfectly! Thanks so much for your help. Here was the final formula:

=IF(AND(LEFT(A26,2)="A1",LEFT(B26,2)="P1"),"High grade",IF(OR(AND(LEFT(A26,2)="A1",LEFT(B26,2)="P2"),OR(AND(LEFT(A26,2)="A2",LEFT(B26,2)="P1"),AND(LEFT(A26,2)="A2",LEFT(B26,2)="P2"))),"Medium grade","N/A"))