Hi Don:

I've made a mess; my ignorance of excel is shining through bright and clear.
I wonder if you could help, once again. I tried your solution to my query
and can't seem to get it to work. I stumbled across a "lookup wizard" that
actually created an Index formula based on a "table?" as follows:

Limit Factor
1mm .10
2mm .20
3mm .30
4mm .40
5mm .50

The formula is
INDEX($d$1:$E$6,MATCH(sheet2!e10,$D$1:$D$6,),MATCH (D8,$D$1:$E$1,)). This
works fine; if I put 5mm in D10, it pulls the rate .50. Where I'm running
into problems is with the "if" statements. The table above is for sales
<=$25mm. Do I have to set up two additional "tables" for sales =$25mm and
=$75mm? If so, how do I get the formula to go to the right table? Do I use
an IF statement? Bascially, I want to say if sales are less than $25mm, go
to table 1, they are greater than $25mm, go to table 2 and then, if they are
greater than $75mm, "refer". Do I have to name the range where the tables
are located (it's the Lotus in me talking) and have the if statement point in
the right direction? I'm getting my applications mixed up. If,
then,else.....I'm sure the logic's the same, it's just the "language".

Any help you can give me would be greatly appreciated!

Thanks Don.

Marg

"Don Guillett" wrote:

glad to help

--
Don Guillett
SalesAid Software

"Margie" wrote in message
...
Thanks Don. I'll give it a shot!

Regards,
Margie

"Don Guillett" wrote:

something like
if(sales??,table1,table2)
then

vlookup(yourvalue,if(sales??,table1,table2),2,fal se)

or you could combine tables and lookup
vlookup(yourvalue,table,2+if(sales??,8,0),false)

--
Don Guillett
SalesAid Software

"Margie" wrote in message
...
I am a new user to Excel; am used to creating formulas in Lotus 123.
I
see
that there are more similarities than differences, however, I'm having
trouble getting started.

Scenario:

I have four variables that require input into cells to return a
number:

Sales: B1 (there are three options: <= 25,000,000, = 25,000,001
but
less than $75,000,000 and if = 75,000,000 "refer".)

Price 1: B2 (this number is hard coded from another worksheet)

Price 2: B3 (this number is hard coded from another worksheet)

Amount: B4; (B4 is either 1,000,000, 2,000,000, 3,000,000, 4,000,000
or
5,000,000.

I have also set up two tables that looks like this

Sales <= $25,000,000
Table name: Factor 1
Limit Factor
1mm .10
2mm .15
3mm .20
4mm .25
5mm .30

Table name: Factor 2
Sales = 25,000,000 but less than 75,000,000
Limit Factor
1mm .10
2mm .15
3mm .25
4mm .30
5mm .50


If sales are less than or equal to 25,000,000 and 1,000,000 is shown
in
B4,
I want to go to the first lookup table , select .10 and mulitply the
number
hard coded in B3 by that factor. I then want to add b2 and the result
of
this formula together. If sales are =25,000,001 but less than
75,000,000,
I
want to go to the second table and do the same calculation.

I'm not sure where to start. I am used to @if statements and have
tried:



=if(b1,<=25,000,000,=if(b4=1,000,000,INDEX("FACTOR "*b3),=if(b4=$2,000,000,IN
DEX("factor"*b3)
That's as far as I've gotten. The INDEX function appears to work the
same
way as lotus, but I can't get the thing to work properly.

So:

Sales $24,333,000
Price 1: $10,000
Price 2: $7,500
Limit: $2,000,000

would work out to .15 X $7,500 = $1,125. $1,125 would then be added
to
$10,000 for a total of $11,125.

I'm getting caught up with the formulas. I'm so used to lotus, that
I'm
confusing myself when I try to get this to work. Could I ask someone
to
get
me started? If I could get an idea of how the formulas are
structured, I
can
build on that.

I hope I haven't made this too confusing; any help would be very much
appreciated!

Thanks.

Margie