Forum Discussion
Excel Formula Help
Example: Pulling the Module column (Column I in DATA)
In the Module column of MRD ABC123 (let’s say column D):
=FILTER(DATA!I:I, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "No matching data")
Explanation:
- DATA!I:I → MRD Module column
- (DATA!G:G="MRD") → District matches MRD
- (DATA!F:F="ABC123") → Job Code matches ABC123
- "No matching data" → optional message if no match
Example: Pulling another column (e.g., Employee Name in Column B)
=FILTER(DATA!B:B, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "")
Step-by-step for all columns on MRD ABC123 tab
- Decide which columns on your tab correspond to which columns on DATA.
- In each column, use:
=FILTER(DATA!<COLUMN>, (DATA!G:G="MRD")*(DATA!F:F="ABC123"), "")
- Replace <COLUMN> with the column from DATA you want to pull.
For other districts/tabs (SLRD, SBRD, etc.)
- Change the District and Module column:
SLRD SLR123 Module (Column J in DATA):
=FILTER(DATA!J:J, (DATA!G:G="SLRD")*(DATA!F:F="SLR123"), "")
SBRD SBR123 Module (Column K in DATA):
=FILTER(DATA!K:K, (DATA!G:G="SBRD")*(DATA!F:F="SBR123"), "")
Notes
- Each column uses its own FILTER formula.
- The rows will automatically spill to match all rows from DATA that meet both criteria.
- This works in Excel 365 / Excel 2021+.
My answers are voluntary and without guarantee!
Hope this will help you.
Hi NikolinoDE, so i thought this was going to work but when i enter it into the other cells its only pulling the first data that matches that criteria. Any other thoughts or ideas?
NikolinoDE *See Sheet Attached* this is what it looks like on my sheet
If you want specific columns (e.g., Name, Job Code, Module)
Use CHOOSE inside FILTER:
MRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!I:I),(DATA!G:G="MRD")*(DATA!F:F="ABC123"),"No matching data")
SLRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!J:J),(DATA!G:G="SLRD")*(DATA!F:F="ABC123"),"No matching data")
SBRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!K:K),(DATA!G:G="SBRD")*(DATA!F:F="ABC123"),"No matching data")
ORD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!L:L),(DATA!G:G="ORD")*(DATA!F:F="ABC123"),"No matching data")
MPRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!M:M),(DATA!G:G="MPRD")*(DATA!F:F="ABC123"),"No matching data")
FP XYZ123 tab (example with different job code)
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!N:N),(DATA!G:G="FP")*(DATA!F:F="XYZ123"),"No matching data")
Replace {1,2,3} with {1,2,3,4,5,...} and add more columns (e.g., DATA!C:C, DATA!D:D, etc.) if you need additional fields to appear in the tab.
Just paste each formula into row 2 of the corresponding tab under your headers, and the results will spill down automatically.
I'm currently working on Excel 2016, so please understand that I can't create a file or test the formulas.
This way, each district tab always pulls all rows that match both criteria and lines them up properly, instead of showing just the first match.
But I still hope it helps you🙂
I understand, im still getting a spill error and selecting the columns i want. I really appreciate your time and help with this. Not sure why it isnt working tho
If you want specific columns (e.g., Name, Job Code, Module)
Use CHOOSE inside FILTER:
MRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!I:I),(DATA!G:G="MRD")*(DATA!F:F="ABC123"),"No matching data")
SLRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!J:J),(DATA!G:G="SLRD")*(DATA!F:F="ABC123"),"No matching data")
SBRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!K:K),(DATA!G:G="SBRD")*(DATA!F:F="ABC123"),"No matching data")
ORD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!L:L),(DATA!G:G="ORD")*(DATA!F:F="ABC123"),"No matching data")
MPRD ABC123 tab
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!M:M),(DATA!G:G="MPRD")*(DATA!F:F="ABC123"),"No matching data")
FP XYZ123 tab (example with different job code)
=FILTER(CHOOSE({1,2,3},DATA!B:B,DATA!F:F,DATA!N:N),(DATA!G:G="FP")*(DATA!F:F="XYZ123"),"No matching data")
- Replace {1,2,3} with {1,2,3,4,5,...} and add more columns (e.g., DATA!C:C, DATA!D:D, etc.) if you need additional fields to appear in the tab.
- Just paste each formula into row 2 of the corresponding tab under your headers, and the results will spill down automatically.
This way, each district tab always pulls all rows that match both criteria and lines them up properly, instead of showing just the first match.
I'm currently working on Excel 2016, so please understand that I can't create a file or test the formulas.
But I still hope it helps you