Advanced filter multiple formulas

[Herbalgiraffe]

Hey all,

I am trying to build an advanced filter that will show only the closest value greater than or equal to two manually entered values. Ideally, Rows 4-5 would be hidden but would serve as the criteria range for the filter. I tried using

 {=MIN(IF(A8:A22>=A2,A8:A22))}

to make it find me the closest value, but I can't seem to find a way to tie the 24 to the 23 in the depth, as the same formula applied to the depth criteria only returns the single smallest value of any other size, not the depth value connected to the correctly found width. If you guys know how to tie the two together so that I can get one line as a result, that would be a huge help to me.

 

[Saba Sabaratnam]

I am not sure if you understand your question fully.

If you want to connect Width to Depth, you can use index formula to do it as demonstrated below.

Enter the following formula in B4

=INDEX(B7:B18,MATCH(A4,A7:A18,0))

Kind regards

Saba

 

[AhoyNC]

Maybe something like this?

Book1
	A	B
1	Width	Depth
2	44	18
3
4	Width	Depth
5	44	22
6
7	Width	Depth
8	24	23
9	38	22
10	44	17
11	44	22
12	52	24
13	58	30
14	64	30
15	70	30
16	72	30
17	80	30
18	42	16
19	51	23
20	59	27
21	65	25
22	70	20
Sheet1

Cell Formulas
Range	Formula
A5	A5	=MIN(IF(A8:A22>=A2,A8:A22))
B5	B5	=IFERROR(MIN(FILTER($B$8:$B$22,($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2))),"No Match")

 

[Herbalgiraffe]

I am not sure if you understand your question fully.

If you want to connect Width to Depth, you can use index formula to do it as demonstrated below.

View attachment 31783

Enter the following formula in B4

=INDEX(B7:B18,MATCH(A4,A7:A18,0))

Kind regards

Saba

Thank you for responding! I thought of that as well, however the issue with index match is that if I enter 44 width and 20 depth as an example using my measurements above, it will return a result of 44 width and 17 depth, since the index match is only looking for the first result, and not making sure that the depth is above the entered measurement. I need the filtered result to show the size that has both measurements being greater than or equal to what is entered.

 

[Herbalgiraffe]

Maybe something like this?

Book1
	A	B
1	Width	Depth
2	44	18
3
4	Width	Depth
5	44	22
6
7	Width	Depth
8	24	23
9	38	22
10	44	17
11	44	22
12	52	24
13	58	30
14	64	30
15	70	30
16	72	30
17	80	30
18	42	16
19	51	23
20	59	27
21	65	25
22	70	20
Sheet1

Cell Formulas
Range	Formula
A5	A5	=MIN(IF(A8:A22>=A2,A8:A22))
B5	B5	=IFERROR(MIN(FILTER($B$8:$B$22,($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2))),"No Match")

I like this idea. I apologize for displaying office 365, I realize that's my fault but currently I am using 2016, which for whatever reason doesn't seem to recognize the FILTER function. Is there a way we could build that same thing with something else 2016 compatible? I will update the version I am using on my profile now sorry again for the confusion.

 

[AhoyNC]

The FILTER function is only available in Excel 365.
I think the INDEX formula below should work.

Book2
	A	B
1	Width	Depth
2	44	20
3
4	Width	Depth
5	44	22
6
7	Width	Depth
8	24	23
9	38	22
10	44	17
11	44	22
12	52	24
13	58	30
14	64	30
15	70	30
16	72	30
17	80	30
18	42	16
19	51	23
20	59	27
21	65	25
22	70	20
Sheet1

Cell Formulas
Range	Formula
A5	A5	=MIN(IF(A8:A22>=A2,A8:A22))
B5	B5	=IFERROR(INDEX($B$8:$B$22,AGGREGATE(15,6,(ROW($B$8:$B$22)-ROW($B$8)+1)/(($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)),1)),"No Match")

 

[Herbalgiraffe]

The FILTER function is only available in Excel 365.
I think the INDEX formula below should work.

Book2
	A	B
1	Width	Depth
2	44	20
3
4	Width	Depth
5	44	22
6
7	Width	Depth
8	24	23
9	38	22
10	44	17
11	44	22
12	52	24
13	58	30
14	64	30
15	70	30
16	72	30
17	80	30
18	42	16
19	51	23
20	59	27
21	65	25
22	70	20
Sheet1

Cell Formulas
Range	Formula
A5	A5	=MIN(IF(A8:A22>=A2,A8:A22))
B5	B5	=IFERROR(INDEX($B$8:$B$22,AGGREGATE(15,6,(ROW($B$8:$B$22)-ROW($B$8)+1)/(($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)),1)),"No Match")

It worked great! I will keep testing it on different combinations but it looks like it did the trick really well. It took me a while to take all that apart and try to understand, and for the most part I get it, but could you explain to me how

(($A$8:$A$57=$A$5)*($B$8:$B$57>=$B$2))

calculates? I get the aggregate is ignoring errors and the first part of the aggregate ends up boiling down to -1, but how that interacts with the rest I can't wrap my head around.

 

[AhoyNC]

This part of the formula just returns an array of numbers for the number of rows in the data set.

(ROW($B$8:$B$22)-ROW($B$8)+1) returns 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 This part ($A$8:$A$22=$A$5) returns TRUE for the values that match cell A5 FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE This part ($B$8:$B$22>=$B$2) returns TRUE for the values that are >= cell B2 TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE When multiplied (($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)) returns (the 1 in the 4th position is the row we want to return) 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 Then when you divide (ROW($B$8:$B$22)-ROW($B$8)+1)/(($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)) or (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)/(0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) We get a #DIV/0! except for position 4 which is the row number we need in the data set. The AGGREGATE function ignores the errors and returns row 4 (in this example) to the INDEX function.

 

[Herbalgiraffe]

This part of the formula just returns an array of numbers for the number of rows in the data set.

(ROW($B$8:$B$22)-ROW($B$8)+1) returns 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 This part ($A$8:$A$22=$A$5) returns TRUE for the values that match cell A5 FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE This part ($B$8:$B$22>=$B$2) returns TRUE for the values that are >= cell B2 TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE When multiplied (($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)) returns (the 1 in the 4th position is the row we want to return) 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 Then when you divide (ROW($B$8:$B$22)-ROW($B$8)+1)/(($A$8:$A$22=$A$5)*($B$8:$B$22>=$B$2)) or (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)/(0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) We get a #DIV/0! except for position 4 which is the row number we need in the data set. The AGGREGATE function ignores the errors and returns row 4 (in this example) to the INDEX function.

That is genius, thank you for explaining!

 

[AhoyNC]

You're welcome. Thanks for the feedback. Don't forget to change your account info to the actual version of Excel you are using.