# Check whether a value appears in one of five columns in another sheet and return a specified value depending on which one it's in

I'm really tired and can't remember how to make this work so would appreciate any pointers on where I'm going wrong.

I'm trying to do the following:

Get cell K2 to provide a specified text value depending on whether the contents of cell B2 matches data in another Sheet on the same document. So, if the value in B2 appears in column F on AltSheet then K2 should return "All", if B2 appears in column G on AltSheet then K2 should return "Spring", etc. The formula needs to check 5 columns. All values are text, and they should match. I'm then dragging this formula to perform the same check for numerous rows/column on the original sheet.

I'm currently looking at this formula.

``````=IF(REGEXMATCH(D69, PlantsSummary!\$F\$2:\$F\$200), "All", (IF(REGEXMATCH(D69, PlantsSummary!\$G\$2:\$G\$200), "Spring", (IF(REGEXMATCH(D69, PlantsSummary!\$H\$2:\$H\$200), "Summer", (IF(REGEXMATCH(D69, PlantsSummary!\$I\$2:\$I\$200), "Autumn", (IF(REGEXMATCH(D69, PlantsSummary!\$J\$2:\$J\$200), "Winter", "Not Found")))))))))
``````

This does return one of the specified values but rarely the right one, and if I drag it down the column I've found that it returns different results for B2 and C2, for example, even if they contain the same value and should produce the same result.

I did try a version of this where rather than checking rows it checked a single cells that was produced by running a JOIN function on the columns but checking the string was more of a hassle.

``````=IF(COUNTIF(PlantsSummary!\$F\$3:\$F\$202, C3)>0, "All", IF(COUNTIF(PlantsSummary!\$G\$3:\$G\$202, C3)>0, "Spring", IF(COUNTIF(PlantsSummary!\$H\$3:\$H\$202, C3)>0, "Summer", IF(COUNTIF(PlantsSummary!\$I\$3:\$I\$202, C3)>0, "Autumn", IF(COUNTIF(PlantsSummary!\$J\$3:\$J\$202, C3)>0, "Winter", IF(COUNTIF(NonForagedIngredients!\$A\$2:\$A\$201, C3)>0, "stock",