0

I have the below table:

enter image description here

Now, I want to check if the subject is having any of the text present in data (col F)

I used:-

=VLOOKUP(A2&"*",F:F,1,0)

But it is giving #N/A.

Not Sure
  • 27
  • 1
  • 7
  • 2
    Other way around - you need to look up column F against column A. You can use a similar approach to that demonstrated [here](https://stackoverflow.com/a/53109606). – BigBen Aug 23 '22 at 19:44
  • @BigBen I see that its only when we are sure that we need to find within x cells. What if I add more rows to column F? then I need to change the formula all the tme. – Not Sure Aug 23 '22 at 19:54

1 Answers1

1

Try:

enter image description here

Formula in B2:

=SUM(COUNTIF(A2,F$2:F$3&"*"))>0

Edit:

=SUM(COUNTIF(A2,F$2:INDEX(F:F,MATCH("ZZZ",F:F))&"*"))>0
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • what if I have value in F4 and other rows in the future? I would need to change the formula always? – Not Sure Aug 23 '22 at 20:43
  • 1
    @NotSure, or you could make values in F into an actual table you refer to or you could for example use a nested `INDEX()` `MATCH()` combination to establish the last used row in the column dynamically. – JvdV Aug 23 '22 at 20:49
  • nested worked very well, just want to check what result you get when you have *Escalation HC 08 23 2022* in your `A4` cell. For me, it's giving result as false. – Not Sure Aug 24 '22 at 07:55
  • Returns `TRUE` as it supposed to for me @NotSure – JvdV Aug 24 '22 at 07:57
  • Please check ss:- https://i.stack.imgur.com/TCkp4.png – Not Sure Aug 24 '22 at 08:00
  • 1
    @NotSure, the only reason I can think of is that you haven't confirmed the formula using `CSE`. Either do that or use `SUMPRODUCT()` instead of `SUM()`. – JvdV Aug 24 '22 at 08:15