# SEARCH whole word when in an array formula

1. ## 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. ## 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"

3. ## Re: SEARCH whole word when in an array formula

That works perfectly, very impressed and thankful.

Cheers
Paul

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

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