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

Hello RyanL2019, I warmly welcome you to the Community. I'm Israel, an Independent Advisor and I would like to give you some advice to solve this problem. I will be exchanging with you to finally solve the problem.

You must see this post, maybe you can find some tracks about it:

https://support.microsoft.com/en-us/office/quic...

Also you can use the short function, see:

https://support.microsoft.com/en-us/office/sort...

If you need a more advanced data procesing, you can consider to use

PowerQuery, see:

https://support.microsoft.com/en-us/office/abou...

I hope that you can solve the problem, please let me know about it. I'm at your disposal to continue supporting you.

Virtual Greetings!

Answer 4

Need to sort the comps from high to low and per bedroom type (see example),

with the other details for each property populating, 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).

 

Sort what "comps"?

What is the "subject Property" at the bottom of the data? Is that the result(s) of the Conditions?

1. Define data as table
2. Turn on the "Filter" feature to show the drop down arrows for sort and filter option

• turn filter on and off in Excel (Ctrl + Shift + L)
  

3. 
4. 

You can clear the filters to apply new conditions.

.

These 2 videos show you how to do filtering.

Using this function requires an educated user (if the question is for sheet used by someone else)

@ Filter: Basic to Advanced                2018 03 01
https://www.xelplus.com/excel-filter-basic-to-advanced/
Excel's AutoFilter is a great feature to quickly display the rows in your data table that meet a certain criteria. The remaining rows that don't match your criteria are automatically hidden. Once you have your selection in view, you can edit the entries or copy the filtered list to another location.
Since there is a lot to filter than meets the eye, I’ve broken it down to two tutorials.
In the filter basic tutorial you’ll learn the following:
.  1.  The shortcut key to turn filter on and off in Excel (Ctrl + Shift + L)
.  2.  How to do approximate matches (filter for words that contain a specific word)
.  3.  How to filter multiple columns
.  4.  How to filter for OR conditions using wildcards
.  5.  How to copy a filtered range to another sheet
.  6.  How to use formulas (such as subtotal) to sum a filtered range
.  7.  How to filter for values between 2 dates
.  8.  How to filter based on color
.  9.  How to turn your data set into an Excel table (Ctrl +T)
. 10.    How to add totals to your Excel table
Advanced Filter Tutorial
.  1  Put your filter values in cells outside of the table – so they are visible all the time
.  2  Advanced Filter Command button and options
.  *  Example 1: filter on 1 column
.     *  Start by entering the header column name(s) to Advanced Filter on
.     *  Enter filter value(s) below the column header names
.  *  Example 2: filter on 2 columns
.  *  Example 3: change filter condition from equals to
.  *  Example 4: wild card filter condition
.  *  Example 5: filter on multiple “OR” conditions
.  *  Example 6: add another column to filters
.  *  Example 7: add “Between” value condition
.  *  Example 8: Generate unique list of values in a new location
.
If you are creating this sheet for someone else to use, the filter criteria can be setup the way you want, let me know and I'll point you to some articles that show how to do that.

.

Answer 5

My apologies. See updated and more clear description in the post and a couple added formulas in the Example file. Needing an INDEX/MATCH kind of solution.