If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula - Return cell content in a matched range search
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
|
|||
|
|||
Formula - Return cell content in a matched range search
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$10 0))),January!$D$8:$D$100,0)) -- Cheers macropod [Microsoft MVP - Word] "Jack" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|