Need Excel vlookup fuctionality in C/R

[Jerhutch]

I am trying to establish some vlookup fuctionality in my Crystal Reports that would be simliar to finding at tax rate in a IRS table based on your income. Something like this:
My Income = 12,000
Get tax rate based on the follow table

low income High income tax rate
0 5,000 10%
5001 10,000 20%
10,001 20,000 30%
20,001 30,000 40%

funtion should bring back 30%
Any help with this would be appreciated.
Jerry

 

[dgillz]

You handle this with an if-then:

If {IncomeField} in 0 to 5000 then .1 else
If {IncomeField} in 5001 to 10000 then .2 else
If {IncomeField} in 10001 to 20000 then .3 else .4

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[IdoMillet]

If you need to let the user maintain the information in an external file, why not in an Access database?

If it has to be a simple text file, you can still use it via an ODBC text driver as a data source to the report.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I agree with both of the solutions here, the one you implement should be based on what you have available.

Ultimately I would use a table within the database itself.

As for a formula, you might use a switch function in a formula, at any rate simplify this:

switch
(
{IncomeField} <= 5000 ,.1,
{IncomeField} <= 10000 ,.2,
{IncomeField} <= 20000 ,.3,
{IncomeField} > 20000 ,.4
)

or using an if:

If {IncomeField} <= 5000 then
.1 else
If {IncomeField} <= 10000 then
.2 else
If {IncomeField} <= 20000 then
.3 else
.4

-k