formula to compare a values and return

Discussion in 'Excel' started by DSnuh, Mar 25, 2015.

  1. DSnuh

    DSnuh

    Joined:
    Mar 25, 2015
    Messages:
    2
    Likes Received:
    0
    I'm looking for a formula to compare text in one specific cell with information in a range of cells within a column. If the text in the original cell matches and of the information in the column of cells, then display a "found"... if not display "not found".

    EXAMPLE
    original cell = 25
    column of cells are random numbers
    if it finds 25 in the column, then return "found"
    if it does not find 25 in the column, then return "not found"
     
    DSnuh, Mar 25, 2015
    #1
    1. Advertisements

  2. DSnuh

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    If the value is entered in B1, one formula would be:

    =IF(ISERROR(SUBSTITUTE(ADDRESS(MATCH(B1,A:A,0),1),"$","")),"not found",B1&" was found in cell "&(SUBSTITUTE(ADDRESS(MATCH(B1,A:A,0),1),"$","")))

    Note that this formula will give the cell reference for the first occurrence of the entered value.
     
    XLPadawan, Jun 15, 2016
    #2
    s.arumugam likes this.
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.