Macro for: If 2 criteria are met, then extract a certain cell data

[Yusra]

I would like to be able to generate a macro that checks if 2 criteria are met. based on the combination of these 2 criteria that are met, I would like excel to return a certain record. In the excel sheet attached, under the Property Revenue & Margin cost sheet, There is a Urban fee Column. This fee is calculated based on the property type and and no. of payments. The urban fee table with the correct calculated amounts can be found under the Property Information sheet.

What I have done to calculate the urban fee, is:
1. Filter the no. of payments (ex. 1)
2. Applied this formula which worked: IF(J2=1,IF(A2="1 BR",'Property Information'!$F$16, IF(A2="2 BR - A ",'Property Information'!$F$17, IF(A2="2 BR - B ", 'Property Information'!$F$18, IF(A2="3 BR ",'Property Information'!$F$19)))))
3. Filter to 2 payments
4. Edited the above formula: IF(J2=2,IF(A4="1 BR",'Property Information'!$G$16, IF(A2="2 BR - A ",'Property Information'!$G$17, IF(A2="2 BR - B ", 'Property Information'!$G$18, IF(A2="3 BR ",'Property Information'!$G$19)))))
5. Filter to 4 payments
6. Formula: IF(J2=4,IF(A2="1 BR", 'Property Information'!$H$16, IF(A2="2 BR - A ",'Property Information'!$H$17, IF(A2="2 BR - B ", 'Property Information'!$H$18, IF(A2="3 BR ",'Property Information'!$H$19)))))
7. Filter to 12 payments
8. Formula: IF(J2=12,IF(A16="1 BR",'Property Information'!$I$16, IF(A2="2 BR - A ",'Property Information'!$I$17, IF(A2="2 BR - B ",'Property Information'!$I$18, IF(A2="3 BR ",'Property Information'!$I$19)))))

While this works, I want a macro that can return the right answer without me having to filter the No. of Payments and editing the formula

 

[vletm]

Yusra
Two samples ...
a) shorter formula: check cell K2
b) with function: check other K-column cells
PS. there were few typos ... and links, which I modify.

 

[Yusra]

Yusra
Two samples ...
a) shorter formula: check cell K2
b) with function: check other K-column cells
PS. there were few typos ... and links, which I modify.

Hi Vletm,

Thank you for the quick response, I have a few questions just to clarify how the formula and function work as I'm not sure I understand that well.

Formula: I didn't understand how the section in bold red works, in particular the nested If statement with the: (J2>1,"s",""). Could you kindly elaborate on this?
=INDEX('Property Information'!F$16:I$19,MATCH(A2,'Property Information'!E$16:E$19,0),MATCH(J2& " Payment"&IF(J2>1,"s",""),'Property Information'!F$15:I$15,0))

Moreover, can I also use a similar combination of the above formula to calculate the Margin profit? I currently have a nested If to get the rent revenue from Property Information Sheet then subtract the Maintenance cost and Urban Fee from it.

Function, I didn't understand it at all.

You also mentioned you changed some links, could you point them out as well. I wasn't able to detect them from my side

 

[vletm]

Yusra
> "s": because Match-function needs exact value, there is ... or ... isn't "s" after "Payment".

> 'bolded' match-function: solves column from 'urban fee'-data
> Yes, You can use same kind of solution with the Margin profit
> Function: Is like =index('Urban Fee'-data, 'Property Type', 'Payments') You can find that function from Module1.
> Links: Your 'Overall Occupancy Rate'-column has links to other file.
eg [Al Jaddaf Case 1).xlsb.xlsx]Property Revenue & Margin Cost'!B2
Check from Your #1 reply file.