Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

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

  1. #1
    Join Date
    10th August 2012
    Posts
    2

    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,

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    11th June 2011
    Location
    Amsterdam
    Posts
    58

    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:
    VB:
    =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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    10th August 2012
    Posts
    2

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th June 2011
    Location
    Amsterdam
    Posts
    58

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    4,024

    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",""),"")
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  6. #6
    Join Date
    11th June 2011
    Location
    Amsterdam
    Posts
    58

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    4,024

    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",""),"")
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: May 31st, 2012, 09:14
  2. Replies: 2
    Last Post: July 17th, 2011, 08:27
  3. Replies: 6
    Last Post: December 1st, 2010, 08:12
  4. Replies: 8
    Last Post: June 12th, 2008, 02:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno