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 where you can enter a 'job type' and have all the materials that are related to that job type pulled through and displayed.
Some materials are used for multiple job types though.
What would be the best way of doing this?
Have tried the FILTER function which works to display the correct materials but requires changing the formula and can't work out how to use this from data entered in a cell.
Can anyone help please?
8 Replies
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!
Let's say Job Type is column A on a sheet named Materials List, and headers in A1:G1.
On the other sheet, enter the text "Job Type" in A1, and use B1 to enter the job type you want to display.
Copy A1:G1 from Materials List to A3:G3 on the other sheet.
Enter the following formula in A4:
=FILTER('Materials List'!A2:G1000, 'Materials List'!A2:A1000=B1, "")
Thanks Hans, I'll try that now.
Have come across another issue - an item might be applicable to more than one job type. Would it be possible to have more than one Job Type column and use IF function to search for the 'Job Type' in those columns?
For example: (5 columns for job type)
IF A1 contains JOB then return data
IF A1 is BLANK then look at column B1
IF B1 contains JOB then return data
IF B1 is BLANK then look at column C1
Thank you so much for your help.
You may try to the following formula-
=FILTER(F2:K9,BYROW(--(B2:E9=N1),SUM),"Not Found") =FILTER('Materials List'!F2:K9,BYROW(--('Materials List'!B2:E9=B1),SUM),"Not Found")
Thank you Hans, I'll try that shortly
Just come across another issue in that an item might relate to more than one job type. If you were to have more than one Job Type column would you use an IF function?
For example; IF A1 contains JOB then return data - IF A1 is blank look to B1... and so on
