# Software

## Question

Locked

### VLOOKUP adding a new cell reference

By ·
I have three cells in an Excel 2003 worksheet.

The cells are Named:
Cell1 = PERIOD
Cell3 = INSTALMENT

PERIOD is a drop down list containing a range of dates:

01/07/2009 ? 30/06/2010
01/01/2009 ? 31/12/2009

PREMIUMBASE is a dollar amount which is manually entered

INSTALMENT will calculate a dollar percentage of PREMIUMBASE dependant on which date range is chosen in PERIOD

I have created a VLOOKUP table as:

COLUMN 1 COLUMN 2
01/07/2009 ? 30/06/2010 2.5%
01/01/2009 ? 31/12/2009 3.5%

An example result would be:

PERIOD 01/07/2009 ? 30/06/2010
INSTALMENT \$25.00 (provided from VLOOKUP table)

The current formula, which is working well, is:

What I would now like to do is add a fourth cell Named FEE

This cell would consist of a simple YES/NO drop down list.

If YES is chosen in this fourth cell the formula will return the given percentage as per the example above.

If No is chosen in this fourth cell the formula will return \$0.00 in INSTALMENT

How can I get this to work correctly as I am at a loss to work it out!!

Any help will be most appreciated.

Regards

Ianmax49

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

### Use an IF statement....

You would have to create one IF statement in the Installment cell which first checks the contents of the 4th cell (Yes/No), then fills with \$0.00 for No or vlookup for Yes.

For example, your formula now is:

It might look like this if the 4th column of data was in D2:

You might not need the parrens around PREMIUMBASE1, so try it without. And, of course, the column containing the formula would be formatted as money (dollar sign and two decimals) AND you could use named references instead of the cell reference of D1.

You can't have the 4th cell updating the contents of your installment cell since it's already looking up something. The 4th cell would overwrite the lookup formula. So, put it all in one cell.

## Related Discussions

• 0

#### Are you subject to professional burnout?

egormishinwork ·

• 2

#### eSports team management software

micagamer818 ·

• 2

zlzpqx ·

• 1

Bumble-bee ·

• 1

ITguy1986 ·