VLOOKUP?

[Guest]

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,INDEX("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

 

[Don Guillett]

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

vlookup(yourvalue,if(sales>??,table1,table2),2,false)

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

 

[Max]

One way

Put in say, B5:

=IF(B1<=25000000,VLOOKUP(B4/1000000&"mm",Factor1,2,0)*B3+B2,IF(AND(B1>250000
00,B1<=75000000),VLOOKUP(B4/1000000&"mm",Factor2,2,0)*B3+B2,IF(B1>75000000,"
Refer","")))

where Factor1 and Factor2
are named ranges refering to:

Factor1
(data below say, in D2:E6)

1mm .10
2mm .15
3mm .20
4mm .25
5mm .30

Factor2
(data below say, in G2:H6)

1mm .10
2mm .15
3mm .25
4mm .30
5mm .50

 

[Guest]

Thanks very much, Max. I'm playing around with the setup now and it seems to
be working just fine! Just needed a push in the right direction.

Regards,
Margie

 

[Guest]

Thanks Don. I'll give it a shot!

Regards,
Margie

 

[Max]

You're welcome, Margie !
Glad to hear you got it working ..

 

[Don Guillett]

glad to help

 

[Guest]

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

=$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]

Why not just ONE table?? It's not clear why you need more. Perhaps a very
clear explanation would help. I would offer to let you send me a workbook
but I will be out of the office all of tomorrow. But, if all else fails send
a SMALL wb anyway.