Use Filter function with 2 criteria to return a specific value

Frank J

Board Regular
Joined
Feb 17, 2011
Messages
104
Office Version
  1. 365
Platform
  1. Windows
I've spent many hours trying to find out why the basic formula for Filter function with 2 criteria will not return a value other than #CALC.
I'm trying to filter the data below for a specific Patron using the Patron ID and a specific Rating by the Date it occurred using the Time In
The data is in a Table and I've shown a sample below, the Rows/Columns are added for reference.

The formula I've tried many variants of is: SORT(UNIQUE(FILTER(A2:Q8,(I2:I8=S1)*(Q2:Q8=T1))))

S1 is the Date and T1 is the Patron ID, I want to be able to use these cells to look at different players as needed.

A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
K​
L​
M​
N​
O​
P​
Q​
1Last NameFirst NamePitTable IDAvg. BetTime (min)Win/LossTheo. WinTime InTime OutCash InChips InFM/Mkr InTotal InTotal OutRating IDPatron ID
2AltemoAl4PJ 404$103.23
160​
0.62​
1/1/2021 2:00:001/1/2021 2:03:14$40$0$0$40$200328855521302541224
3Refused Name44197.08381CC 101$255.02
(50)​
2.51​
1/1/2021 2:00:401/1/2021 2:05:41$300$0$0$300$250328855393310258222
4Refused Name44197.084252JP 204$155.15
(85)​
1.49​
1/1/2021 2:01:191/1/2021 2:06:28$55$50$0$105$20328855433310258223
5WangLai8MB 801$800490.97
(1,700)​
4,516.89​
1/1/2021 2:02:091/1/2021 10:13:07$0$3,200$0$3,200$1,500328855490310078426
6MosleySam1CC 101$300637.73
(1,800)​
3,826.40​
1/1/2021 2:02:211/1/2021 12:40:05$1,800$500$0$2,300$500328855501310258140
7FuChang2MB 208$50114.78
(950)​
66.00​
1/1/2021 2:02:571/1/2021 3:57:44$0$19,500$0$19,500$18,550328855534303463825
8SteptoeAlbert3HH 307$2080.93
(75)​
25.47​
1/1/2021 2:03:101/1/2021 3:24:06$100$0$0$100$25328855550310229947
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If S1 has a date, rather than date/time try
Excel Formula:
SORT(UNIQUE(FILTER(A2:Q8,(INT(I2:I8)=S1)*(Q2:Q8=T1))))
 
Upvote 0
What is in S1 & T1?
Also are the date/times in col I real dates, or just text that looks like dates?
 
Upvote 0
S1 I typed in the date I want and T1 is the Patron ID to find as well
Both formatted properly
 
Upvote 0
If you format col I as General what do you see?
 
Upvote 0
The actual workbook I'm searching through has 200,000 entries and I can't change the date as it's linked to too many others
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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
Back
Top