Announcement

Collapse
No announcement yet.

Find value in a range and return Y/N if adjacent cell contains specific text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find value in a range and return Y/N if adjacent cell contains specific text

    Hi all,

    I am having a lot of difficulty with this.

    I am looking for a formula to do the following:


    First I want it to check whether the text in my cell Sheet1 A1 is present in a range in Sheet3 i.e. A1:A250 (which I know it may be in multiple instances).
    If the text value of A1 Sheet1 is present, I want an adjacent cell in the row to be checked for a specific text e.g. "352FR1".
    If text value "352FR1" is present in an adjacent cell to any row containing my text value from A1 Sheet1 then I want the value Yes returned in my formulated cell!!


    Very simple!!

    I can provide further info as required!

    cheers,

  • #2
    Re: Find value in a range and return Y/N if adjacent cell contains specific text

    If you want to avoid using VBA a solution might be to create another column in Sheet3 ("C") which concatenates column A and B. Now you only have to check for a single text, namely the text in range A1 and "352FR1". If you do not want to add another column, you can also use an array formula:
    Code:
    =NOT(ISERROR(MATCH(CONCATENATE(A1;"352FR1");CONCATENATE(Sheet3!A1:A250;Sheet3!B1:B250;0))))
    As with any array formula, use Shft+Cntrl+Enter to enter it.

    Comment


    • #3
      Re: Find value in a range and return Y/N if adjacent cell contains specific text

      HI,

      Thanks for that. I think this will work perfectly but I am having some trouble with the formula.

      I am returning an error when entering it. Excel does not seem to want to accecpt (A1;"352FR1") it is returning an error on the A1 part.

      Any ideas?

      Comment


      • #4
        Re: Find value in a range and return Y/N if adjacent cell contains specific text

        Maybe you need to use "," instead of ";" as a seperator? Otherwise I do not have a clue for now.

        Comment


        • #5
          Re: Find value in a range and return Y/N if adjacent cell contains specific text

          Try:

          =IF(ISNUMBER(MATCH(A1,Sheet3!$A$1:$A$250,0)),IF(VLOOKUP(A1,Sheet3!$A$1:$B$250,2,0)="352FR1","YES",""),"")
          Where there is a will there are many ways. Finding one that works for you is the challenge!

          MS Excel MVP 2010-2016

          Comment


          • #6
            Re: Find value in a range and return Y/N if adjacent cell contains specific text

            Doesn't that only check whether the value "352FR1" is next to the first occurence of A1? It could of course be that "352FR1" is next to a later occurence.

            Comment


            • #7
              Re: Find value in a range and return Y/N if adjacent cell contains specific text

              True, then perhaps:

              =IF(ISNUMBER(MATCH(A1,Sheet3!$A$1:$A$250,0)),IF(COUNTIFS(Sheet3!$A$1:$A$250,A1,Sheet3!$A$1:$A$250,"352FR1")>0,"YES",""),"")

              or if you have pre XL2007...

              =IF(ISNUMBER(MATCH(A1,Sheet3!$A$1:$A$250,0)),IF(SUMPRODUCT(--(Sheet3!$A$1:$A$250=A1),--(Sheet3!$A$1:$A$250="352FR1"))>0,"YES",""),"")
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment

              Working...
              X