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...
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")