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 -