Formula - Return cell content in a matched range search

[Jack]

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

 

[macropod]

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" 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