Formula to increase a value by % based on two different types of data.

[SMExcel]

Hi All,

I am having trouble trying to work this one out. I can't figure out the formula that goes in the green cells highlighted below. In the green cells I want to increase the cost base of each property by the % from the data below, based on whether the property is allocated as a house/unit, and what postcode it belongs to. For example. Property 1's cost-base of $200k would increase by 3.5% as it's a house and has the postcode 3500. Therefore the amount in the green adjustment cell will be $7,000.

Really appreciate any feedback.

Cheers


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Property Name		Postcode	Cost Base	Adjustment	New Adjusted Value
Property 1	House	3500	200000
Property 2	Unit	3400	300000
Proprety 3	House	3300	400000

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="131"></colgroup><tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Data - Increase in Market Value
House	3500	3.50%
Unit	3500	2.00%
House	3400	3.00%
Unit	3400	1.25%
House	3300	3.25%
Unit	3300	1.75%

<colgroup><col style="width: 100px"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[nikio8]

If you change your table, Column names Postcode, house% and unit%
you could use =vlookup(postcode, PHU table, match(propertyType, PHU table headings,0),0)

vlookup to get the right row match or hlookup to get column.

 

[nardagus]

Is this what you were after?

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Property Name		Postcode	Cost Base	Adjustment	New Adjusted Value
2
3	Property 1	House	3500	200000	7000
4	Property 2	Unit	3400	300000	3750
5	Proprety 3	House	3300	400000	13000
6
7
8	Data - Increase in Market Value		%
9
10	House	3500	3,50%
11	Unit	3500	2,00%
12	House	3400	3,00%
13	Unit	3400	1,25%
14	House	3300	3,25%
15	Unit	3300	1,75%

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E3 =D3*SUMIFS($C$10:$C$15,$A$10:$A$15,B3,$B$10:$B$15,C3) E4 =D4*SUMIFS($C$10:$C$15,$A$10:$A$15,B4,$B$10:$B$15,C4) E5 =D5*SUMIFS($C$10:$C$15,$A$10:$A$15,B5,$B$10:$B$15,C5) <tbody> </tbody>

<tbody>
</tbody>

 

[SMExcel]

To nardagus' reply -

That does work for the first two properties, but when I use the formula for property 3 it comes up as 0 in E5. It also comes up as 0 when I change the house postcode to 3400 or 3300, any idea why this might be happening?

 

[SMExcel]

If you change your table, Column names Postcode, house% and unit%
you could use =vlookup(postcode, PHU table, match(propertyType, PHU table headings,0),0)

vlookup to get the right row match or hlookup to get column.

Can you please explain how the table should be layed out and i'll try work it around for the vlookup

 

[nardagus]

Hmmm. For Property3 Adjustment should be $13,000?
Then it works for me... Are you sure there is no error in a formula for this Property? Did you copy a formula from E3 to E4 and E5? If yes it should work...

 

[nikio8]

Property Name Type Postcode Cost Base Adjustment New Adjusted Value Property 1 House 3500 200000 =VLOOKUP(C2,$A$9:$C$11,MATCH(B2,$A$8:$C$8,0),0) Property 2 Unit 3400 300000 =VLOOKUP(C3,$A$9:$C$11,MATCH(B3,$A$8:$C$8,0),0) Proprety 3 House 3300 400000 =VLOOKUP(C4,$A$9:$C$11,MATCH(B4,$A$8:$C$8,0),0) Postcode House Unit 3500 3,50% 2,00% 3400 3,00% 1,25% 3300 3,25% 1,75% <tbody> </tbody>

<tbody>
</tbody>