# filter formula

#### Brentsa

##### Board Regular
Good day,

I have a data table on one tab. In this data table I have two columns with numbers in them Column I and Column J. I am using two filters to look for a specific number but want to combine these two formulas so that is the number does not appear in Column I it will look for it in Column J.
My two separate formulas are as follow:

=IFERROR(FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$I\$7:\$I\$1000=a3),"")
=IFERROR(FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$J\$7:\$J\$1000=I3),"")

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### MARK858

##### MrExcel MVP
Maybe...
Excel Formula:
``=IFERROR(FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$I\$7:\$I\$1000=A3,FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$J\$7:\$J\$1000=I3)),"")``
or
Excel Formula:
``=FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$I\$7:\$I\$1000=A3,FILTER('Teller Stats'!\$F\$7:\$L\$1000,'Teller Stats'!\$J\$7:\$J\$1000=I3,""))``

#### Brentsa

##### Board Regular
Thanks Mark both options work

#### MARK858

##### MrExcel MVP
You're welcome (2nd formula is better btw, just depends if you are trying to pick up some error which isn't because the value isn't found)

#### Peter_SSs

##### MrExcel MVP, Moderator
IF you have the LET function in your 365 then you could also consider
Excel Formula:
``=LET(rng,'Teller Stats'!F7:L1000,CI,INDEX(rng,0,4),FILTER(rng,IF(COUNTIF(CI,A3),CI=A3,INDEX(rng,0,5)=I3),""))``

Replies
1
Views
51
Replies
3
Views
84
Replies
198
Views
1K
Replies
3
Views
76
Replies
1
Views
443

1,127,449
Messages
5,624,827
Members
416,057
Latest member
VARSHA V VASWANI

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