Forum Discussion
best way of returning data from a table
Hi I have a worksheet showing a materials list column headers include Job Type, Item number, description, purchase cost, quantity, price, vat Would like to be able to have a separate worksheet wher...
Hi NicNic​
Great question especially since you mentioned the limitations of the FILTER function when dealing with multiple job types and external inputs.
Best Way to Return Table Rows by Job Type (Works with Multiple Job Types Per Row)
If you're using Excel 365 or Excel 2021, the FILTER function is perfect for pulling related materials into a separate sheet based on a selected Job Type even when rows include multiple job types (e.g., "Plumbing, Electrical").
=FILTER(A2:G20, A2:A20=J1, "No materials found")
ADVANCED: REUSABLE LAMBDA FUNCTION
If you're using Excel 365 with Named LAMBDA functions, this lets you reuse the filter logic everywhere.
Step-by-Step:
- Go to Formulas → Name Manager
- Click New
- Name it: FilterByJobType
- Formula: =LAMBDA(table, col, type, FILTER(table, LOWER(col)=LOWER(type), "No match found"))
=FilterByJobType(A2:G100, A2:A100, J1).
Hope this helps!