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

Thank you for your work on this! Very much appreciated.

Answer 4

Thank you, Lz!

Re: Merging. No problem. I tried deleting the first one (before posting the second) since it became evident that I wasn't very clear.

Re: Dashes. They are actual dashes rather than a formatting. They make problems for formulas like SUMPRODUCT, as I'm sure you know.

Re: Subject Property. This is needed to compare to the others and see where it falls within the range.

Re: Your solution. This is great. Thank you! I like the way you think. You definitely simplified some things that I wouldn't have thought to do. I'll keep this in mind and think on the Tables.

I posted above that I solved it, 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?

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

Answer 5

Hi Ryan2019

You are in the real estate industry, your extracted dataset, and the query datasets need to be dynamic (meaning it has to be able to expand and shrink).

Scenario:

So in the middle of the day, you are in the field showing clients the possibilities, you find out that there's an updated Listing dataset.

You go download the updated listing dataset to update your solution and it happens:

1. The updated listing "shrank"
2. The updated listing "expanded"

and you're in between showing clients at properties.

If your solution is formula based, then you'll have to manipulate your solution to compensate for the expansion/reduction of the updated listing because its does not automatically expand or reduce dynamically, thereby you'll have to copy and paste your formulas to adapt your listing solution.

Wouldn't it be better if after you downloaded the updated dataset all you had to do was press ctrl+alt+F5 to update the dataset and the specific queries relevant to your clients?

The only solution to your situation is dynamic tables with dynamic parameter queries to update everything in a snap. download the updated listing then refresh everything bam! done!