Return cell content in a matched range

[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

 

[T. Valko]

Try this...

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

 

[Max]

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