1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Formula - Return cell content in a matched range search

Discussion in 'Microsoft Office' started by Jack, Feb 11, 2010.

  1. Jack

    Jack Flightless Bird

    I need help with the following formula:
    =IF(ISERROR(SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)))=FALSE,SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)),"")
    This formula works well for Sum total for all matches however I need to use
    it to return a word instead of a SUM .
    Example: Column D contains the folowing payment methods. ATM, Check, Draft
    I want to use the formula to find the match of A1 in the range F8-F100 and
    return what payment method was used located in Column D on the matched row.
    As you can tell it is a nested formula. (Can't copy the nest symbol)
    The formula will return a blank if there aren't any match in the range.
    NOTE: there should be only one match in the range of F8-F100 so there should
    only be one result in range D8-D100. I'm using this formula on another page
    of the excell book to copy the payment method.
    Thank you
    Jack
    PS this is the first time i've used this site for help
     
  2. macropod

    macropod Flightless Bird

    Hi Jack,

    Try:
    =IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),"My Text","")

    And, instead of your existing formula, try:
    =SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0))

    --
    Cheers
    macropod
    [Microsoft MVP - Word]


    "Jack" <Jack@discussions.microsoft.com> wrote in message news:1A8979F1-6FC3-4CE9-ACBD-5342064AFBD8@microsoft.com...
    >I need help with the following formula:
    > =IF(ISERROR(SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)))=FALSE,SUM(IF(NOT(ISERROR(SEARCH($A$1,January!$F$8:$F$100))),January!$D$8:$D$100,0)),"")
    > This formula works well for Sum total for all matches however I need to use
    > it to return a word instead of a SUM .
    > Example: Column D contains the folowing payment methods. ATM, Check, Draft
    > I want to use the formula to find the match of A1 in the range F8-F100 and
    > return what payment method was used located in Column D on the matched row.
    > As you can tell it is a nested formula. (Can't copy the nest symbol)
    > The formula will return a blank if there aren't any match in the range.
    > NOTE: there should be only one match in the range of F8-F100 so there should
    > only be one result in range D8-D100. I'm using this formula on another page
    > of the excell book to copy the payment method.
    > Thank you
    > Jack
    > PS this is the first time i've used this site for help
     

Share This Page