0

I have a spreadsheet with thousands of rows and numerous columns. What I am hoping to accomplish is to basically check column AB for a certain value. When it finds a row that contains (not exact match, just needs to include) the word "test" then I want to check if column Z (in the same row) has value "next". If both those are true, then I want to display the value from column N in that same row. What code could I use to accomplish this?

Note: I need this to list the value from column N for each row that has both "test" and "next", not just for one row.

Edit:

To clarify what I mean by multiple values see this image:

enter image description here

I want a formula that will list ALL of the Column N values of rows that meet have both test and next. And this list is not in the row, it is going to be one column listing each match.

Update:

I have gotten close to getting this done, this is my best formula up to this point. =ArrayFormula(IF(ISNUMBER(MATCH("*"&$A$1&"*",SID!AB:AB,0)),SID!N3,"n")) This works once, but here is what I need fixed:

This returns the first value that works, in this example Place1, but then it just lists everything form column C, even if it does not reach the requirements. Most likely an issue with my arrayformula.

Jake P
  • 121
  • 1
  • 2
  • 9

1 Answers1

0

Try out the below formula,

=IF(AND(ISNUMBER(MATCH("*"&"test"&"*",A1,0)),ISNUMBER(MATCH("*"&"next"&"*",B1,0))),"Column N value","")

enter image description here

This formula searches cell A1 for the string test (anywhere in it) and searches next in cell B1. I leave the part of replacing the cells with the required columns to you and also change the Column N value in the formula to N1

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
  • This is working great, but one thing that maybe I did not make clear in the question is that I need to do this to return multiple values. So exactly what this does, but I need it to list the value in column N for each row that has both "test" and "next". – Jake P Jul 20 '17 at 16:06
  • @JakeP Am not understanding correctly. May be showing an example will help me to help you – Gowtham Shiva Jul 20 '17 at 16:09
  • @JakeP Did you try changing the value "Column N Value" with N1? – Gowtham Shiva Jul 20 '17 at 16:10
  • @JakeP did you try what i said? changing the value "Column N Value" with N1 – Gowtham Shiva Jul 20 '17 at 16:27
  • I did try that, that works for one row but I have a sheet with thousands of rows. I need this to search a whole column at once, it will need to use array forumlas. – Jake P Jul 20 '17 at 16:31
  • `=IF(AND(ISNUMBER(MATCH("*"&"test"&"*",A1,0)),ISNUMBER(MATCH("*"&"next"&"*",B1,0))),N1,"")` just try this drag it down or double click on the fill handle – Gowtham Shiva Jul 20 '17 at 16:36
  • This did not quite work yet. See the update on my post, I have modified this and almost have it working. If you can find my error that would be great. – Jake P Jul 27 '17 at 13:39