Return cell content in a matched range
 

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

Return cell content in a matched range
 

Try this...

=IF(COUNTIF(January!F8:F100,A1),INDEX(January!D8:D 100,MATCH(A1,January!F8:F100,0)),"")

--
Biff
Microsoft Excel MVP


"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

Return cell content in a matched range
 

A more generic expression would be an index/match, which can match & return
text or numbers, even for fuzzy matches like what is happening here

With A1 containing your lookup value to be fuzzy searched within F2:F100
you could try this in say, B1, press normal ENTER to confirm will do:
=INDEX(D$2:D$100,MATCH(TRUE,INDEX(ISNUMBER(SEARCH( A1,F$2:F$100)),),0))

If you need an error trap to return neat looking blanks for unmatched cases,
shape it like this: =IF(ISNA(MATCH(...)),"",INDEX/MATCH(...))
Success? Immortalize this post, hit the YES below
--
Max
Singapore
---
"Jack" wrote:
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