• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Yusra

New Member
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
 

Attachments

Yusra

New Member
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

Excel Ninja
Yusra
> "s": because Match-function needs exact value, there is ... or ... isn't "s" after "Payment".
Screenshot 2019-08-18 at 09.19.57.png
> '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.
 
Top