Conditional formatting, multiple IF/AND statement trouble

Question

Here's what my spreadsheet looks like. I got to the end when I realized I had left out a logical function for when to use the letter "y" as a consonant and when to use it as a vowel.

What's going on here is row 22 (cell AI22 in particular) is saying if the corresponding letter in row 5 is a vowel, use the numerical equivalent in row 22 (the vowel values,) but if it's a consonant then just use "0" (for row 23 I said "if row 22 is "0" then use the numerical equivalent of whatever letter is in row 5, basically... row 22 and 23 separate vowels from consonants.) I need to modify the formula in the command line to logically decide whether or not the individual use of the letter "y" is or is not a vowel. Here's my concept of how to achieve this, I just can't figure out how to word it so there are no errors and I can trust it to work every time without fail because all of this is hidden and the values are called back to a printable page where all you see is an overall number.

Here's my current formula in AI22...

=IF(AI5="","",IF(AI5="A",1,IF(AI5="E",5,IF(AI5="I",9,IF(AI5="O",6,IF(AI5="U",3,0))))))

I need it to also say something like... "IF AI5 = "Y" AND there is a consonant on both sides (or blank on one side and consonant on the other) then that use would be a vowel with a numerical value of 7. IF AI5 = "Y" AND it is either before OR after a vowel, then that use would be a consonant. EXAMPLE: in the word "FLY" the "Y" is a vowel, in the word "FLYING" it is not because of the "I" being added. So in the above screen shot (the red "Y"s,) the "Y" in Gwyneth should be a vowel, which as you can see by cell BR17 (First Vowel) the formula is incorrect. In the middle name Kelly, the "Y" is a vowel because of the consonant that precedes it, and in the last name Bryant, the "Y" is a consonant because it's next to a vowel.

Any help would be greatly appreciated and I know there were probably WAY easier ways to produce these results then what I used, but it is what it is... thanks to all in advance.

Answer 1

Thanks for the response, I'll remember the formula because I see implications for its use elsewhere. This will work to a point, but is there something that can be added to it so it knows whether a "y" is to be used as a consonant or a vowel so it knows whether to use a value of 7 or 0?

"I need it to also say something like... "IF AI5 = "Y" AND there is a consonant on both sides (or blank on one side and consonant on the other) then that use would be a vowel with a numerical value of 7. IF AI5 = "Y" AND it is either before OR after a vowel, then that use would be a consonant. EXAMPLE: in the word "FLY" the "Y" is a vowel, in the word "FLYING" it is not because of the "I" being added. So in the above screen shot (the red "Y"s,) the "Y" in Gwyneth should be a vowel, which as you can see by cell BR17 (First Vowel) the formula is incorrect. In the middle name Kelly, the "Y" is a vowel because of the consonant that precedes it, and in the last name Bryant, the "Y" is a consonant because it's next to a vowel."

Answer 2

Hi,

try this formula

=IF(AI5="","",LOOKUP(AI5,{"A","E","I","O","U","Y"},{1,5,9,6,3,7}))