Forum Discussion
Re: best way of returning data from a table
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.
4 Replies
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")Could you give us an idea of what the two sheets look like, by attaching screenshots, or a sanitized copy of the workbook without sensitive data?
Does this do what you want?
=LET(filtered, FILTER('Material List Original'!A2:I1000, ('Material List Original'!A2:A1000=B1)+('Material List Original'!B2:B1000=B1)+('Materials List Original'!C2:C1000=B1), ""), IF(filtered="", "", filtered))
