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

@Rayan2019

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

OK, I see now and that makes sense. However, as your column [Distance] has "n/a" on that row and you have a criteria on [Distance], I don't see how the "Subject Property" will ever be reported...

=> Formula updated to inc. that row

Re: Dashes. They are actual dashes rather than a formatting

You like to complicate your life :). Excel (and its competitors) like numbers, so wherever possible use numbers instead of text values - check out on this forum and others the number of people who raised the famous issue, i.e. Why is my SUM(...) = 0?. You won't believe it

If you want to see a Dash enter 0 in those cells and use a Custom Format where the 3rd "argument" is "-"

=> Formula revised to exclude the 0 + MaxPropertyOnTarget (renamed MaxPropertiesOnTargetOneBed) to do the same and formatted the cells as explained above

Re: Do you mind explaining to me why this part works and what it's even doing, please?

Well, on a previous case I already broke down an array formula for you => Refer to it as array calculations are always the same process

Re: And on C27, why does it take the first instance of $536 (Property 3) out of Match's lookup array (and so on)?

The ISNA(MATCH(C$3:C$20;C$25:C25;0)) part excludes the Properties already found. So, in C26 none are excluded as the content of C25 (Property) doesn't exist in C3:C20. Now assume in C26 we have reported "Property 7", then on C27 "Property 17" is excluded (= FALSE) in the array

Re: Is that related to the first part ( IF(ROWS($1:2)>MaxPropertiesOnTarget,"" ) or something else in the formula?

No, see above. The IF(ROWS($1:2) > MaxPropertiesOnTarget,"",...) is there to avoid doing IFERROR(...,"") and performing unecessary calculations

MaxPropertiesOnTarget reports the number of properties meetings the different criteria. So on each row (starting C26) we check if we exceeded the value of MaxPropertiesOnTarget. If that's the case we return "" and don't perform the array calculations => it's all about efficiency

Re. (The formula you spent a lot of time). Do you know of a way to tweak it...?

I won't spend time on this at least for the following reasons:

• In my 1st reply I mentioned you were matching n times the same thing - MATCH("Subject*",$B$3:$B$20,0). This makes no sense to me from an efficiency perspective => Name a cell on that row and you're done, you have the refererence you need
• Your formula doesn't account for the "Dashes" (in P11) you want to exclude...
• With the IFERROR approach unecessary array calculations are performed. Assume a range of 1000 rows instead of your current range. Does it really makes sense to perform 997 array calculations when only 3 Properties meet the various criteria???

Revised version of the workbook is available here

Hope this helps

Answer 4

Thank you!

Re: Dashes. They actually produce from the pdf which I export to Excel, which I then copy into my template for analysis. Going forward, I can set up some VBA to remove all dashes automatically. I'd rather them not be there anyways.

Re: Array formula. I'm tracking with that. Didn't realize your version was an array. I spent most of yesterday researching using Index/Match as array so I could incorporate that into my version (wasn't successful). Now abandoned.

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

I've incorporated all of yours into my larger template, but the 3-bed section isn't working for some reason. I copied everything exactly (so I think, at least) but changed the starting point for the exclusion range. I also named more ranges and used the newly named ranges for MaxPropertiesOnTarget. Do you mind looking to see why it's working except for in that one section? It looks to me like the array part is malfunctioning. Maybe it needs regular ranges in it rather than named ranges in some parts? Idk. Workbook here.

Answer 5

@Ryan2019

Is this challenge over now? If so, could you close it by marking as answer what works for you? Can help others... Thanks