+ Reply to Thread
Results 1 to 5 of 5

IF statement and SEARCH

  1. #1
    Registered User
    Join Date
    06-03-2011
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    46

    IF statement and SEARCH

    =IF($E3="Y",IF($B3=(SEARCH($O$2,LEFT(B:B,5))),$A3,""),"")


    I want to include a search function for the first 5 digits in column B and include it in an IF statement. I am not doing it right as I get a VALUE error.


    Column B3 has a list of codes. I have a code in O2 (e.g.12345) and I want to only use the first 5 digits of numbers in column B (may have 123456789) and test it against o2. If there is a match in the first 5 digits of B3 to o2 then put A3 in the cell else blank etc.



    Thanks.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: IF statement and SEARCH

    Hi Maymano,

    You switch between referencing a cell, B3, and all of column B (as well as column B3, which doesn't exist). Are you trying to create a formula for one cell? Or are you trying to create THIS formula and put it in cell, say, C3. You'll then copy it down to C4, C5, C6, etc.?

    In your example, what if the match was in B829? Would you still want it to pull the value from A3, or from A829?

  3. #3
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: IF statement and SEARCH

    If your numbers are in text format, you could use:

    =IF($E3="Y",IF(COUNTIF(B:B,CONCATENATE(O2&"*"))>0),$A3,""),"")

    ?

  4. #4
    Registered User
    Join Date
    06-03-2011
    Location
    Florida, USA
    MS-Off Ver
    Excel 2013
    Posts
    46

    Re: IF statement and SEARCH

    yes, want to copy it down so number of rows change.

    if the match was in b829, i would want to use a829. I have pinned the columns but not the rows. thanks.

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: IF statement and SEARCH

    This may work for you, if I'm understanding correctly:

    =IF(AND($E3="Y",LEFT($B3,5)+0=$O$2),$A3,"")

    The "+0" in the middle assumes the data you type into cell O2 is a number and the cell is formatted as a number. The LEFT function returns text, so if you try to compare the text string "12345" to the number 12345, you won't get a match.

    If there can be text in O2 (or in column B data) you can exclude the +0.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1