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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|