#### Herbalgiraffe

##### New Member
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
Excel Formula:
`` {=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. ### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Saba Sabaratnam

##### Board Regular
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

##### Well-known Member
Maybe something like this?
Book1
AB
1WidthDepth
24418
3
4WidthDepth
54422
6
7WidthDepth
82423
93822
104417
114422
125224
135830
146430
157030
167230
178030
184216
195123
205927
216525
227020
Sheet1
Cell Formulas
RangeFormula
A5A5=MIN(IF(A8:A22>=A2,A8:A22))
B5B5=IFERROR(MIN(FILTER(\$B\$8:\$B\$22,(\$A\$8:\$A\$22=\$A\$5)*(\$B\$8:\$B\$22>=\$B\$2))),"No Match")

#### Herbalgiraffe

##### New Member
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

##### New Member

Maybe something like this?
Book1
AB
1WidthDepth
24418
3
4WidthDepth
54422
6
7WidthDepth
82423
93822
104417
114422
125224
135830
146430
157030
167230
178030
184216
195123
205927
216525
227020
Sheet1
Cell Formulas
RangeFormula
A5A5=MIN(IF(A8:A22>=A2,A8:A22))
B5B5=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

##### Well-known Member
The FILTER function is only available in Excel 365.
I think the INDEX formula below should work.

Book2
AB
1WidthDepth
24420
3
4WidthDepth
54422
6
7WidthDepth
82423
93822
104417
114422
125224
135830
146430
157030
167230
178030
184216
195123
205927
216525
227020
Sheet1
Cell Formulas
RangeFormula
A5A5=MIN(IF(A8:A22>=A2,A8:A22))
B5B5=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

#### Herbalgiraffe

##### New Member

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

Book2
AB
1WidthDepth
24420
3
4WidthDepth
54422
6
7WidthDepth
82423
93822
104417
114422
125224
135830
146430
157030
167230
178030
184216
195123
205927
216525
227020
Sheet1
Cell Formulas
RangeFormula
A5A5=MIN(IF(A8:A22>=A2,A8:A22))
B5B5=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
Excel Formula:
``((\$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

##### Well-known Member
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

#### Herbalgiraffe

##### New Member
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

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

Replies
3
Views
155
Replies
7
Views
34
Replies
6
Views
37
Replies
3
Views
241
Replies
12
Views
102

### Forum statistics

1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back