vlookup based on a different cell

[Scott A]

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

 

[Pete_UK]

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

 

[Scott A]

Thank You Pete! That works perfectly, your awesome!

Scott A

 

[Pete_UK]

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

Pete