VLookup on two rows from bottom to top

[USAMax]

I have the Owner Statement for rental properties that I need to summarize. I am trying to get the name of the current tenant that will be at the bottom of the list.

Right now cell B1 is a drop-down of all properties. When a property is selected, that property's transactions are filtered.

I need the last cell in column D that matches B1 in column B and with "Rent Income" in column C.

Data header row is on row 12 and goes to row 322 but I will be adding data each month.

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=INDEX($D$12:$D$500,AGGREGATE(14,6,(ROW($D$12:$D$500)-ROW($D$12)+1)/($B$12:$B$500=$B$1)/($C$12:$C$100="Rent Income"),1))

 

[USAMax]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=INDEX($D$12:$D$500,AGGREGATE(14,6,(ROW($D$12:$D$500)-ROW($D$12)+1)/($B$12:$B$500=$B$1)/($C$12:$C$100="Rent Income"),1))

Wow, impressive and above my skill set. I would never have come up with this. It works amazingly!

I have updated my Account Details,

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

I have updated my Account Details,

Cheers