Thread
:
VLOOKUP?
View Single Post
#
7
Don Guillett
Posts: n/a
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
Reply With Quote