Follow-up: Create anything based on the Word/Phrase... III

This exercise was originally posted at: http://maritimeorca0.blogspot.com/2015/07/create-anything-based-on-wordphrase-ii.html

The Task

Create anything from the random word/phrase provided.

Random Word/Phrase

Concoctions

My Creation

I found this to be a word with potential so broad that it was hard to figure out what specifically to work on. You can create pretty much anything at all and it will fit the term. My Excel obsession had me thinking about some form of spreadsheet project. Then I realized that the word is pluralized. Either I could make multiple concoctions, or I could use pluralization as a way to narrow down the possibilities. I would create a spreadsheet that can pluralize words.

I wasn't going to go through a dictionary and add plurals for everything. Instead, I decided to put together formulas (there are multiple acceptable plurals for that word) that would check a list for any manually entered plurals. If there wasn't a listing, it would follow the general rule.

Let's start with the irregular table. I have a quick list of irregular plurals with the singular followed by the plural form. This has been named as tblIrregular and is located on the Irregular worksheet.

The other table is a little trickier. It seems to me that the last 2 letters usually indicate the pluralization. I have decided to create a table showing the last two letters of a word and what they should be replaced with. The table has been named tblLast and it has been included in the Last 2 worksheet.

Both tables are incomplete. If an error message shows up, a new condition can be added to the Last 2 table. If you need to add an irregular plural, you can type in the singular and plural in the irregular table.

The formula starts by checking if there is an irregular plural listed. This can be displayed as:
=IF(COUNTIF(tblIrregular[Singluar],B1)>0

If there is an irregular plural, it will look up the noun and return the corresponding plural:
VLOOKUP(B1,tblIrregular,2,FALSE)

If there is no irregular, it will replace the final two letters with the pluralized letters. This part of the formula is a little more complicated.
LEFT(B1,LEN(B1)-2)&VLOOKUP(RIGHT(B1,2),tblLast,2,FALSE)

I will break this one down. Excel will subtract two from the length of the word. It will then return the text up to that length, effectively dropping the final two letters. Excel will then take the 2 characters of the far right, look for the characters in the Last 2 table, then return the corresponding characters indicated for the plural. The left and right components will be combined into a single word.

The final formula:
=IF(COUNTIF(tblIrregular[Singluar],B1)>0,VLOOKUP(B1,tblIrregular,2,FALSE),LEFT(B1,LEN(B1)-2)&VLOOKUP(RIGHT(B1,2),tblLast,2,FALSE))

This spreadsheet can be found at: https://onedrive.live.com/redir?resid=2E6EFB80915ED5BD!3899&authkey=!AAqovFn84ZsR4wg&ithint=file%2cpdn

Feel Free to Comment

Now that this exercise has concluded, feel free to share any thoughts that you had regarding results, your approach, and any other ideas that popped in your head regarding this exercise.

Upon completion of this exercise, please fill out the following form:
https://docs.google.com/forms/d/17gfhH7k1nd1Ex-ws4c9sZBYNi-BH33DMb1q6EEhT4ZY/viewform?usp=send_form
Responses to the above form are collected and stored in a spreadsheet that can be viewed at:
https://docs.google.com/spreadsheets/d/17DwBAlEmXI37DqF3OLqv8Xmflw92u5cbacHXxMD9LOA/pubhtml