# How to find specified values among various fields.

Hello,

We need to write a formula which finds specified values among various fields. For example;

We wantto find the value "1A3" (string) in A1, A2, A3, A4, A5,...,A40 fields in a report and if there is one or more "1A3" values in any of these fields, our formula will take the value "S3".

Asking this because we need to search for lots of values like "1A3" in about 40 fields and assign a new value with this formula and it is really hard to do that by using the basic if-then-else structure. So we need 2 find out if there is a practical way.

With Best Regards.
TAI
SOLUTION
Rajesh Dalmia

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In the above formula u have to mention all the field names one by one and have to use the + sign in them

rdonline1
SOLUTION

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

rdonline1 and Pete... Thank you so much for your help. Both answers are cool for me. I'll split points 2 both of you, but there's a problem with stringvar solution;

its working great up to 14 fields. but its not working more than 14?
in that case u can try using

If InStr({A1} + {A2}+ {A3}+ {A4}+ ... + {A14},"1A3",1) >= 1 OR InStr({A15 + {A2}+ {A3}+ {A4}+ ... + {A29},"1A3",1) >= 1 OR InStr({A30} + {A2}+ {A3}+ {A4}+ ... + {A44},"1A3",1) >= 1Then
"S3"
Else
"1A3"

like this

rdonline1

thank you rdonline, but it would be gr8 if i could make it by using stringvar. because i'll search for lots of values in these fields and i dont want to see

i'd be appreciated if anyone could help about stringvar...

wbr.
Which version of CR are you using?

Pete

Pete, I'm using CR XI.

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
TAI - There shouldn't be a limit of the number of fields but there is on size.  Are these fields very large?  - frodoman

Thank you bdreed35,

but it didnot work..  gives an error to formula; "the result of formula cannot be an array"...

TAI-

Can you post the exact formula that you are using and then tell us where the highlighting starts when the error occurs?

bdreed35,

its okay now, made a mistake and corrected it. Thank you.

Frodoman;

3 characters for each fields i dont know whats wrong but i saw some errors about stringvar solution. cause its not working for every record and i dont know why...

Looks like Using arrays solved my problem. Thank you all. I'll give 300 points to bdreed, 100 points to rdnline1 and 100 points to pete.

Thank you.

With best regards.

TAI