A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Return cell content in a matched range



 
 
Thread Tools Display Modes
  #1  
Old February 11th, 2010, 11:04 PM posted to microsoft.public.excel.worksheet.functions
Jack
external usenet poster
 
Posts: 463
Default 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
  #2  
Old February 11th, 2010, 11:18 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Return cell content in a matched range

Try this...

=IF(COUNTIF(January!F8:F100,A1),INDEX(January!D8 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



  #3  
Old February 11th, 2010, 11:55 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default 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$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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.