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

Hi RyanL,

The Low High I wasnt able to incorporate because your case study sample file will yield a zero hit (nothing to see).

and here's the file ==> Comp Sample.xls

Power Query was used, you can use the solution as is, or you can incorporate it as a step towards your final solution.

cheers

Here's a screenshot:

Answer 4

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)),"")