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.

Thanks in advance.

1 Answer 1


I poked at this a bit more and found my (rather long-winded but functional) solution:

=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",

(IF(C3="", "","Not Found"))))))))

This checks a column range (in PlantsSummary!G) for a value (in C3), returns a text value ("All") if it finds the value, and moves on to check 4 more columns in sequence. It then checks a column in another sheet, eliminates instances where the original value (in C3) was blank, and ultimately returns "Not Found" if it can't find the value anywhere.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.