Excel Forum

Searching for words from a list in a range on a separate sheet

Mike Green

Active Member

Members

Forum Posts: 5

Member Since:
March 5, 2023

Offline

March 6, 2023 - 2:07 am

Hi,

I have searched for a solution and found a few close examples, but nothing I have been able to modify to fit my needs.

This is what was provided with a spreadsheet I currently use. The formula extracts specific words from the cell and fills the cell with whatever I want. I can manually change the words in the formula, add or subtract the number of nested functions to make it work, but I have several item specifics lists I need to add for other product categories. I would like to be able to have a separate sheet with the lists. Size, Type, Color...etc.

=IF(ISERROR(FIND(" Jan ",A2)),IF(ISERROR(FIND(" Feb ",A2)),IF(ISERROR(FIND(" Mar ",A2)),IF(ISERROR(FIND(" Apr ",A2)),IF(ISERROR(FIND(" May ",A2)),IF(ISERROR(FIND(" Jun ",A2)),IF(ISERROR(FIND(" Jul ",A2)),IF(ISERROR(FIND(" Aug ",A2)),IF(ISERROR(FIND(" Sep ",A2)),IF(ISERROR(FIND(" Oct ",A2)),IF(ISERROR(FIND(" Nov ",A2)),IF(ISERROR(FIND(" Dec ",A2)),IF(ISERROR(FIND(" January ",A2)),IF(ISERROR(FIND(" February ",A2)),IF(ISERROR(FIND(" March ",A2)),IF(ISERROR(FIND(" April ",A2)),IF(ISERROR(FIND(" May ",A2)),IF(ISERROR(FIND(" June ",A2)),IF(ISERROR(FIND(" July ",A2)),IF(ISERROR(FIND(" August ",A2)),IF(ISERROR(FIND(" September ",A2)),IF(ISERROR(FIND(" October ",A2)),IF(ISERROR(FIND(" November ",A2)),IF(ISERROR(FIND(" December ",A2)),IF(ISERROR(FIND(" Winter ",A2)),IF(ISERROR(FIND(" Spring ",A2)),IF(ISERROR(FIND(" Summer ",A2)),IF(ISERROR(FIND(" Fall ",A2)),IF(ISERROR(FIND(" 24 ",A2)),""),"September|October|November"),"June|July|August"),"March|April|May"),"December|January|February"),"December"),"November"),"October"),"September"),"August"),"July"),"June"),"May"),"April"),"March"),"February"),"January"),"December"),"November"),"October"),"September"),"August"),"July"),"June"),"May"),"April"),"March"),"February"),"January")

I would like to be able to have a tables or ranges like below on a different sheet, that could be the data or list of words to search for, with the corresponding results. Being able to change to length of the lists and simply adjust the range to fit. Range references being absolute obviously.