Advanced filter multiple formulas

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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.


1613003246524.png
 

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
Joined
May 26, 2018
Messages
196
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.

1613008041030.png


Enter the following formula in B4

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

Kind regards

Saba
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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")
 
Solution

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 22, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback. Don't forget to change your account info to the actual version of Excel you are using.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top