Match and If formula

building reports webintelligence
1

I have some fields that, for some unknown reason, contains a sales person’s name and a date is appended to the end and there are multiple dates for each sales person. So far no one can tell why or how the dates got in a name field.

My challenge is to create a summary report by sales person. I am using the Match function with If in WebI XI.

Alone these formulas work perfectly. When I attempt to join them I get an error. the error is “Invalid character at position 53 ;”

There will be a lot more sales reps to add but until I can get the first two to work, there is no need adding the rest.

=If(Match([Outside Sales Rep];“Will*”);“Will K”)
=If(Match([Outside Sales Rep];“Jerry*”);“Jerry N”)

It does not like the “;” at the second “if”

I have been away from building reports too long, I guess. I cannot see what is probably very obvious.

=If((Match([Outside Sales Rep];“Will*”);“Will K”);If(Match([Outside Sales Rep];“Jerry*”);“Jerry N”))

So, who would like to shed the light on the obvious for me?

REB01 :us: (BOB member since 2004-11-29)

2

This worked for me:
=If(Match([Outside Sales Rep]; “Will*”)) Then “Will K” ElseIf(Match([Outside Sales Rep]; “Jerry*”)) Then “Jerry N”

msr :us: (BOB member since 2008-04-24)

3

=If((Match([Outside Sales Rep];“Will*”);“Will K”);If(Match([Outside Sales Rep];“Jerry*”);“Jerry N”))

); before “Will K” should just be ;
Same for before “Jerry N”

When you say dates are appended, would it be easier just to strip the dates from the names?

4

Hi,

Use the Nested If condition. The syntax is:
If test_value Then true_value [Else false_value|ElseIf test_value Then true_value [Else
false_value…]]

Ex: =If Match([Outside Sales Rep];“Will*”) Then “Will K” Elseif Match([Outside Sales Rep];“Jerry*” THen “Jerry N” Elseif…

chaitanya_b (BOB member since 2011-11-21)

5

I think the extra brackets (highlighted in red) in the first “if” may be causing trouble - remove these…you should also include the “else” bit (in cyan)

=If ( (Match([Outside Sales Rep];“Will*”);“Will K”) ;If(Match([Outside Sales Rep];“Jerry*”);“Jerry N”;“Other”))

mpwalker :uk: (BOB member since 2008-11-06)

6

You could just strip the numbers - check Marek’s variable part way down this topic:

7

Thanks everyone for your input. I tried each suggestion and finally got this formula to work.

=If(Match([Outside Sales Rep];“Will*”);“Will Keresy”;If(Match([Outside Sales Rep];“Jerry*”);“Jerry Nichols”;[Outside Sales Rep]))

Now, as long as I repeat the process another dozen times, I will be great. :rotf:

Before I do that, however, I am going to give Marek’s idea a try. Mark, thanks for the lead! :+1:

REB01 :us: (BOB member since 2004-11-29)