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

 

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