VLookup Question

[mjmills]

I have a table (see below):

<table width="422" border="0" cellpadding="0" cellspacing="0" height="68"><col style="width: 82pt;" width="109"> <col style="width: 67pt;" width="89" span="2"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 82pt;" width="109" height="17">
</td> <td class="xl25" style="width: 67pt;" width="89">MIN</td> <td class="xl25" style="width: 67pt;" width="89">MAX</td> <td class="xl25" style="width: 48pt;" width="64">PE ratio</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Project Size:</td> <td class="xl25">$0</td> <td class="xl25">$1,000,000</td> <td class="xl25">$250,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$1,000,001</td> <td class="xl25">$4,000,000</td> <td class="xl25">$500,000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25">$4,000,001</td> <td class="xl25">$100,000,000</td> <td class="xl25">$750,000
</td> </tr> </tbody></table>
And I have Construction Projects. I bid the projects and I want Excel to calculate the PE - Project Equivalent.

For example, I have a project that is $3.5M. I see that $3.5M is between $1M and $4m so the PE Ratio is $500,000 (which means I need to return 1 PE for every $500,000 of the project's value). In this case the PE for the project would be $3.5M/$500,000=7.

Example 2, I have a project that is $800K. I see that $800K is between $0 and $1m so the PE Ratio is $250,000 (which means I need to return 1 PE for every $250,000 of the project's value). In this case the PE for the project would be $800,000/$250,000=3.2.

I need to be able to enter the value of the construction project and have Excel return the number of PE's (Project Equivalents) by looking at the table to see what PE Ratio to use, then dividing the project value by that number.

I'm thinking this is a VLOOKUP Function but I can't find an example of it. Any suggestions? Do you have a video that would help explain how to answer this question?

 

[LxQ]

Yes, a vlookup works, so let's say your amount is in cell A1, and your PE table is in B1:D3
then use:

=A1/VLOOKUP(A1,B1:D3,3,True)

 

[mjmills]

Wow. Thanks for that. It worked well.

There is one additional element. I forgot to mention it until I entered your vlookup answer.

If I have a project that is less than $250,000, I need the answer to always going to be 1 PE. So, for example, when I used the vlookup formula you provided, one of the projects I have is for $90K and the answer returned by the formula was 0.36. I need to round that to 1. But I don't want any of the other answers to be rounded up.

That's because, if we have a $90K project, it is just as difficult to do as a $250K project but once they start going over $250K, they start getting a little easier to manage.

I can do that manually if there is no solution. We don't ususally have more than 10 - 15 active projects at any one time.

 

[LxQ]

try this:

=A1/MIN(A1,VLOOKUP(A1,B1:D3,3,TRUE))

basically, this devides A1 by the lesser or either you PE value or your project value. Above $250,000 your project value will be greater than you PE value, so it will always look up the PE, and under $250,000 it will divide the project value by itself, giving you 1.