vlookup based on a different cell
 

Hello,

I am trying to use the vlookup formula for the spreadsheet below. I can't
seem to get it to work. Can someone help me with this and am I using the
right formula?

What I am looking to do is to have the spreadsheet automaticaly figure out
what multiplier to use for each project where the "X" is showing below. in
other words for line 19 the cell where the "X" is (E19) would look at D19 and
see that it is 29%, then look up in the section 1 and see that 29% is below
30% so it would use line 10 and put the multiplier from C10 in the cell. The
way it needs to look up information is that each row in section 1 in cell A
is the margin at the lowest % up to the next % listsed in the cell above. In
other words below 15% would be a mulitplier of 50%, 15%-29% is a multiplier
of 63% all the way up to 40% and then anything above 40% would have a
multiplier of 125%. Is the Section 1 table set up correctly or do I need to
change it any way also?

Section 1
A B C
6 Margin Comm % Multiplier
7 40% 25% 125%
8 35% 25% 113%
9 30% 25% 100%
10 25% 25% 88%
11 20% 25% 75%
11 15% 25% 63%
11 Below 15% 25% 50%

Section 2
A B C D E
19 Projects Contract Gross Margin Multiplier
Sold Margin %
20 Project A 70000.00 20000.00 29% X
20 Project B 20000.00 6000.00 30% X
20 Project C 15000.00 20000.00 13% X

I am at a total loss on this one! Any help is greatly appreciated!

Thanks,
Scott A

vlookup based on a different cell
 

Arrange your section 1 table in ascending order, like this:

Margin Comm Multiplier
0% 25% 50%
15% 25% 63%
20% 25% 75%
25% 25% 88%
30% 25% 100%
35% 25% 113%
40% 25% 125%

The 0% replaces your "Below 15%", and is assumed to be in cell A7.

I understood your first X to be in row 20, with the headers in row 19,
so put this in F20:

=INDEX(C$7:C$13,MATCH(E20,A$7:A$13,1))

and copy down.

Hope this helps.

Pete

On Dec 10, 10:38*pm, Scott A wrote:
Hello,

I am trying to use the vlookup formula for the spreadsheet below. *I can't
seem to get it to work. *Can someone help me with this and am I using the
right formula?

What I am looking to do is to have the spreadsheet automaticaly figure out
what multiplier to use for each project where the "X" is showing below. *in
other words for line 19 the cell where the "X" is (E19) would look at D19 and
see that it is 29%, then look up in the section 1 and see that 29% is below
30% so it would use line 10 and put the multiplier from C10 in the cell. *The
way it needs to look up information is that each row in section 1 in cell A
is the margin at the lowest % up to the next % listsed in the cell above. In
other words below 15% would be a mulitplier of 50%, 15%-29% is a multiplier
of 63% all the way up to 40% and then anything above 40% would have a
multiplier of 125%. *Is the Section 1 table set up correctly or do I need to
change it any way also?

Section 1
* * * * * *A * * * * * *B * * * * * * * C
6 * * *Margin * Comm % * *Multiplier
7 * * * *40% * * * *25% * * * * 125%
8 * * * *35% * * * *25% * * * * 113%
9 * * * *30% * * * *25% * * * * 100%
10 * * *25% * * * *25% * * * * *88%
11 * * *20% * * * *25% * * * * *75%
11 * * *15% * * * *25% * * * * *63%
11 *Below 15% * 25% * * * * *50%

Section 2
* * * * * A * * * * * * *B * * * * * * *C * * * * * *D * * * * * E * * *
19 *Projects * * Contract * * Gross * *Margin *Multiplier *
* * * * Sold * * * * * * * * * * * *Margin * * *% * * * *
20 *Project A * 70000.00 *20000.00 * 29% * * * * X *
20 *Project B * 20000.00 * *6000.00 * 30% * * * * X *
20 *Project C * 15000.00 *20000.00 * 13% * * * * X

I am at a total loss on this one! Any help is greatly appreciated!

Thanks,
Scott A

vlookup based on a different cell
 

Thank You Pete! That works perfectly, your awesome!

Scott A

"Pete_UK" wrote:

Arrange your section 1 table in ascending order, like this:

Margin Comm Multiplier
0% 25% 50%
15% 25% 63%
20% 25% 75%
25% 25% 88%
30% 25% 100%
35% 25% 113%
40% 25% 125%

The 0% replaces your "Below 15%", and is assumed to be in cell A7.

I understood your first X to be in row 20, with the headers in row 19,
so put this in F20:

=INDEX(C$7:C$13,MATCH(E20,A$7:A$13,1))

and copy down.

Hope this helps.

Pete

On Dec 10, 10:38 pm, Scott A wrote:
Hello,

I am trying to use the vlookup formula for the spreadsheet below. I can't
seem to get it to work. Can someone help me with this and am I using the
right formula?

What I am looking to do is to have the spreadsheet automaticaly figure out
what multiplier to use for each project where the "X" is showing below. in
other words for line 19 the cell where the "X" is (E19) would look at D19 and
see that it is 29%, then look up in the section 1 and see that 29% is below
30% so it would use line 10 and put the multiplier from C10 in the cell. The
way it needs to look up information is that each row in section 1 in cell A
is the margin at the lowest % up to the next % listsed in the cell above. In
other words below 15% would be a mulitplier of 50%, 15%-29% is a multiplier
of 63% all the way up to 40% and then anything above 40% would have a
multiplier of 125%. Is the Section 1 table set up correctly or do I need to
change it any way also?

Section 1
A B C
6 Margin Comm % Multiplier
7 40% 25% 125%
8 35% 25% 113%
9 30% 25% 100%
10 25% 25% 88%
11 20% 25% 75%
11 15% 25% 63%
11 Below 15% 25% 50%

Section 2
A B C D E
19 Projects Contract Gross Margin Multiplier
Sold Margin %
20 Project A 70000.00 20000.00 29% X
20 Project B 20000.00 6000.00 30% X
20 Project C 15000.00 20000.00 13% X

I am at a total loss on this one! Any help is greatly appreciated!

Thanks,
Scott A

.

vlookup based on a different cell
 

You're welcome, Scott - thanks for feeding back.

Pete

On Dec 11, 7:03*am, Scott A wrote:
Thank You Pete! That works perfectly, your awesome!

Scott A



"Pete_UK" wrote:
Arrange your section 1 table in ascending order, like this:

Margin * * Comm * * Multiplier
*0% * * * * * * 25% * * * * 50%
15% * * * * * *25% * * * * 63%
20% * * * * * *25% * * * * 75%
25% * * * * * *25% * * * * 88%
30% * * * * * *25% * * * *100%
35% * * * * * *25% * * * *113%
40% * * * * * *25% * * * *125%

The 0% replaces your "Below 15%", and is assumed to be in cell A7.

I understood your first X to be in row 20, with the headers in row 19,
so put this in F20:

=INDEX(C$7:C$13,MATCH(E20,A$7:A$13,1))

and copy down.

Hope this helps.

Pete

On Dec 10, 10:38 pm, Scott A wrote:
Hello,

I am trying to use the vlookup formula for the spreadsheet below. *I can't
seem to get it to work. *Can someone help me with this and am I using the
right formula?

What I am looking to do is to have the spreadsheet automaticaly figure out
what multiplier to use for each project where the "X" is showing below. *in
other words for line 19 the cell where the "X" is (E19) would look at D19 and
see that it is 29%, then look up in the section 1 and see that 29% is below
30% so it would use line 10 and put the multiplier from C10 in the cell. *The
way it needs to look up information is that each row in section 1 in cell A
is the margin at the lowest % up to the next % listsed in the cell above. In
other words below 15% would be a mulitplier of 50%, 15%-29% is a multiplier
of 63% all the way up to 40% and then anything above 40% would have a
multiplier of 125%. *Is the Section 1 table set up correctly or do I need to
change it any way also?

Section 1
* * * * * *A * * * * * *B * * * * * * * C
6 * * *Margin * Comm % * *Multiplier
7 * * * *40% * * * *25% * * * * 125%
8 * * * *35% * * * *25% * * * * 113%
9 * * * *30% * * * *25% * * * * 100%
10 * * *25% * * * *25% * * * * *88%
11 * * *20% * * * *25% * * * * *75%
11 * * *15% * * * *25% * * * * *63%
11 *Below 15% * 25% * * * * *50%

Section 2
* * * * * A * * * * * * *B * * * * * * *C * * * * * *D * * * * * E * * *
19 *Projects * * Contract * * Gross * *Margin *Multiplier *
* * * * Sold * * * * * * * * * * * *Margin * * *% * * * *
20 *Project A * 70000.00 *20000.00 * 29% * * * * X *
20 *Project B * 20000.00 * *6000.00 * 30% * * * * X *
20 *Project C * 15000.00 *20000.00 * 13% * * * * X

I am at a total loss on this one! Any help is greatly appreciated!

Thanks,
Scott A

.- Hide quoted text -

- Show quoted text -