INDEX/MATCH to Sort with Conditions?

Question

What is the best way to do this? Need to sort the comps from high to low and per bedroom type (see example) using a formula, with the other details for each property populating via formula, too. The sort also needs to be according to the conditions on the right (within a certain year range, within a certain distance, and ignoring the dashes).

The rest of the table is easy to populate via INDEX/MATCH based upon column C, but I'm not sure how to incorporate the other conditions. Will need to be for 2019 versions of Excel and prior. Thank you in advance!

Example file here: 1drv.ms/x/s!Aufhrt05Ok7YhJV17mTherbZ1Q9TqQ?e=PpZ2qs

EDIT:

I found a couple ways late yesterday, but apparently duplicates are a problem and my Index/Match stops at the first match every time. I can set it up to prevent duplicate rents, but I still need those properties listed out. Is there a way to tweak the below so that Match still goes past the first match.

What I'm working with in C26:

=IFERROR(INDEX($C$3:$C$20,MATCH(LARGE(IF(($D$3:$D$20<=(INDEX($D$3:$D$20,MATCH("Subject*",$B$3:$B$20,0))+$P$7))*($D$3:$D$20>=(INDEX($D$3:$D$20,MATCH("Subject*",$B$3:$B$20,0))-$O$7))*($M$3:$M$20<=$P$9),$H$3:$H$20),ROW(1:1)),$H$3:$H$20,0)),"")

Original screenshot example:

Answer 1

@Ryan2019

Re: Revised workbook. Your link goes to a different one. Can you resend?

:((( Sincere apologies. I was working on something else in //. Updated the link in my previous reply, same one here

Re. ...but the 3-bed section isn't working for some reason

If you got the correct link yesterday you would have seen that I renamed (I mentioned it) MaxPropertiesOnTarget as MaxPropertiesOnTargetOneBed. Reason is: to exclude cells = 0 COUNTIFS must refer to the appropriate range (formula with your named ranges):

and there's no way to dynamically change CompsBedOne with i.e. CompsBedTwo as you have 3 different reports. So, I duplicated the above formula (changing the appropriate CompsBedXxx range) and named them:

• MaxPropertiesOnTargetOneBed
• MaxPropertiesOnTargetTwoBed
• MaxPropertiesOnTargetThreeBed
• MaxPropertiesOnTargetStudio

I (hopefully) fixed your example with the above named formula. At the top of each report you'll see i.e.:

so you can easily check during your simulations

Hope this helps

Answer 2

@Ryan2019

(it's me who merged your 2 = threads). Couple of notes:

• Ignore "-" is useless. The - is only a representation of 0 when a cell is formatted as accounting
• Not sure why your Index, Match... did include the Subject Property row (except if I misunderstood), changed to C3:C19
• Named a few cells for clarity and more importantly to avoid matching n times the same thing
• In Name Manager: MaxPropertiesOnTarget counts the number of properties meeting the criteria This avoids unecessary calculations hidden with IFERROR(..., "")

in C26 (pic. as this site currently bugs re. formatting):

Updated sample available here

PS: If it was me I would work with Tables (instead of ranges), at least to put clarity in my formulas, not to mention dynamic ranges. You want something that works with previous versions of Excel, fyi Tables exist since Excel 2007. In this article, a few benefits of Tables vs Ranges

Answer 3

Perfect! This is great. Thank you for your time and the work you put into this in helping me here! This will save me a lot of time.

 

Do you have any VBA advice on automatically hiding the unused rows as well as the entire section (2 bed, 3 bed, studio, etc.) if the subject property doesn't have a rent figure entered? I don't want to have to click a button or use Worksheet_Calculate, as those tend to bog down the template. What I originally planned was to have a hidden helper column (e.g., column z or something) and use VBA to have the value be there what the value is in, for example, C57. But I see now that that only worked with my data validation boxes in another report I use, and I still have the problem of VBA not hiding the row when the value rendered from a formula changes. Is Worksheet_Calculate the only option?

You're welcome. Glad I could help

Re. VBA. There are contributors on this forum who are much more experienced than me. If you can't find something good enough by yourself, feel free to raise a new case

Answer 4

And do you have anything or an article you could point me to that expands on using Match with an array like you did?

I'm afraid not :( IMHO it's all about practicing, praticing... starting with 1 creteria and a very few i.e. Properties and to decompose each array in columns side by side

Answer 5

Roger that. Thank you again! Do you blog anywhere?