+ Reply to Thread
Results 1 to 3 of 3

SEARCH whole word when in an array formula

  1. #1
    Registered User
    Join Date
    09-07-2009
    Location
    Lytham, England
    MS-Off Ver
    Excel 2003+07
    Posts
    75

    SEARCH whole word when in an array formula

    I have the following formula

    ={SUMPRODUCT((ISNUMBER(SEARCH(A1,'Mar Subm'!$G$2:$G$3000)))*('Mar Subm'!$B$2:$B$3000)*IF(RIGHT('Mar Subm'!$C$2:$C$3000,1)="M",12,1))}

    Col G contains unique refereneces i.e. 123ABC, Col B has a premium amount and Col C has an M or Y for payment frequency.

    It is used to recall the amount of annualised premium (multiplied by 12 if 'M') based on what the user types in cell A1.

    The user should type something like 123ABC in A1 to return data for that unique record. I have entered my formula in such a way that if A1 is blank then it gives a total figure for everything.

    I am having a problem though if the user just types a number in (e.g. if they type a 3 in then they get all records with a 3 in).

    Does anyone have a solution that would work so that it only returns when the whole string is matched not part?

    I know I could just limit the user to select certain unique 123ABCs but I will shortly be using this formula on company names and would run into trouble if I had company names like "The Dog", "The Big Dog", "The Dog Big" or "The Big Dog Company" as it could return a lot more than I bargained for if I typed say "The Dog" in.

    Many Thanks

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SEARCH whole word when in an array formula

    Try this version

    =SUMPRODUCT((('Mar Subm'!$G$2:$G$3000=A1)+(A1="")>0)+0,(RIGHT('Mar Subm'!$C$2:$C$3000)="M")*11+1,'Mar Subm'!$B$2:$B$3000)

    doesn't need "array entry"
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-07-2009
    Location
    Lytham, England
    MS-Off Ver
    Excel 2003+07
    Posts
    75

    Re: SEARCH whole word when in an array formula

    That works perfectly, very impressed and thankful.

    Cheers
    Paul

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1