List multiple values based on Criteria in Excel 2016
Hi experts,
What simple formula is there in Excel to list multiple values based on multiple criteria's?
The result should be like the Filter formula from Google Sheets.
I used this formula so far but it only shows the last found value instead of all.
The formula came from article
https://www.excel-bytes.com/how-to-extract-a-dynamic-list-with-multiple-criteria-in-excel/
What simple formula is there in Excel to list multiple values based on multiple criteria's?
The result should be like the Filter formula from Google Sheets.
I used this formula so far but it only shows the last found value instead of all.
=IFERROR(INDEX(STOCK!$D:$D,SMALL(IF( $A$2=STOCK !$Q:$Q,ROW (STOCK!$D: $D)),ROW(1 :1))-1,1), ””)
The formula came from article
https://www.excel-bytes.com/how-to-extract-a-dynamic-list-with-multiple-criteria-in-excel/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In Mr. Rempel's webpage, he used an IF function to return either the row number of the matching value or FALSE. From that array of values, he subtracted 1. He did that because the range he was testing started in row 2 and he needed an index number (starting with 1) to give the right reference to the INDEX function.
Having posted this kind of solution many times in the past, I know that Askers often post questions with different layouts than their actual workbook. If the data starts on row 2, you need to subtract 1 to get a proper index number as shown on Mr. Rempel's webpage.But if the data started on row 4, you would need to subtract 3. The innocent looking -1 in Mr. Rempel's formula needs to change with the worksheet layout. Since I hate getting "doesn't work" responses to my suggestions, I use an equivalent expression -ROW(STOCK!$D$2)+1. The result of that expression is -1 for the given layout--but would change (correctly) to -3 if the data actually started on row 4. Even though the resulting formula is longer, I find that most Askers adapt it correctly to their real worksheet layout.
When you simplified Mr. Rempel's formula by using entire columns, you should have removed the -1 from his formula. As a result, you shot yourself in the foot twice: once because using entire columns in array-entered formulas is very slow, and a second time because you didn't remove the -1 and were therefore returning values from the row above the match.
Having posted this kind of solution many times in the past, I know that Askers often post questions with different layouts than their actual workbook. If the data starts on row 2, you need to subtract 1 to get a proper index number as shown on Mr. Rempel's webpage.But if the data started on row 4, you would need to subtract 3. The innocent looking -1 in Mr. Rempel's formula needs to change with the worksheet layout. Since I hate getting "doesn't work" responses to my suggestions, I use an equivalent expression -ROW(STOCK!$D$2)+1. The result of that expression is -1 for the given layout--but would change (correctly) to -3 if the data actually started on row 4. Even though the resulting formula is longer, I find that most Askers adapt it correctly to their real worksheet layout.
When you simplified Mr. Rempel's formula by using entire columns, you should have removed the -1 from his formula. As a result, you shot yourself in the foot twice: once because using entire columns in array-entered formulas is very slow, and a second time because you didn't remove the -1 and were therefore returning values from the row above the match.
You might also wonder why I was dividing the ROW by the criteria matching expression.
With a second parameter of 6, AGGREGATE ignore error values. This is a very useful property, because dividing by the criteria test will return the numerator (ROW) if the test passes, and an error value if it fails. AGGREGATE ignores the error values, and returns the nth smallest index number.
Another useful property of AGGREGATE is that it doesn't need to be array-entered when using array expressions if the first parameter is 14 or higher.
With a second parameter of 6, AGGREGATE ignore error values. This is a very useful property, because dividing by the criteria test will return the numerator (ROW) if the test passes, and an error value if it fails. AGGREGATE ignores the error values, and returns the nth smallest index number.
Another useful property of AGGREGATE is that it doesn't need to be array-entered when using array expressions if the first parameter is 14 or higher.
ASKER
Thanks for the comprehensive explanation. I think you already helped me before with this kind of formula. The row part got me confused, so I couldn't adapt it correctly.
What is the purpose of this row formula
What is the purpose of this row formula
ROW(1:1)and
ROW(STOCK!$D$2)+1?
ROW(1:1) returns the series 1, 2, 3, ... as you copy the formula down. The formula is using that expression to specify the lowest, second lowest, third lowest, etc. index number.
-ROW(STOCK!$D$2)+1 equals -1. So why not simplify the formula by using -1? I have found over the years that posting the simplified form often results in "doesn't work" when Askers adapt the formula to a different range in their real workbook. I get more "great answer" responses when I post the longer formula using -ROW(STOCK!$D$2)+1.
You need to convert the row numbers returned by ROW(STOCK!$D$2:$D$1000) into index numbers. The INDEX function always needs index numbers. Row numbers in that example will vary between 2 and 1000, but the corresponding index numbers vary between 1 and 999. The conversion between row numbers and index numbers in this example is to subtract 1. But if we were dealing with INDEX(STOCK!$D$4:$D$1000),
You need to understand how and when to convert between row numbers and index numbers when using a formula that subtracts a fixed offset like the -1 in Mr. Rempel's formula. While some people want to understand a formula, others just want one that works. By using -ROW(STOCK!$D$2)+1 in the formula, most people can adapt it to a different range without needing to thoroughly understand how the formula works.
-ROW(STOCK!$D$2)+1 equals -1. So why not simplify the formula by using -1? I have found over the years that posting the simplified form often results in "doesn't work" when Askers adapt the formula to a different range in their real workbook. I get more "great answer" responses when I post the longer formula using -ROW(STOCK!$D$2)+1.
You need to convert the row numbers returned by ROW(STOCK!$D$2:$D$1000) into index numbers. The INDEX function always needs index numbers. Row numbers in that example will vary between 2 and 1000, but the corresponding index numbers vary between 1 and 999. The conversion between row numbers and index numbers in this example is to subtract 1. But if we were dealing with INDEX(STOCK!$D$4:$D$1000),
You need to understand how and when to convert between row numbers and index numbers when using a formula that subtracts a fixed offset like the -1 in Mr. Rempel's formula. While some people want to understand a formula, others just want one that works. By using -ROW(STOCK!$D$2)+1 in the formula, most people can adapt it to a different range without needing to thoroughly understand how the formula works.
ASKER
Yes, you are right. Thank you so much for your patience and your willingness to explain in-depth.
ASKER
Okay good to know in which version of Excel the filter function is available.
The following formula works for me. Can you explain the "row" part of this formula to me, so I can remake this kind of formula myself?