INDEX COLLECT with criteria matching in one column OR another
I have an equipment PM submission log that receives data from an electronic form and feeds "Date Performed" into the corresponding cell on a master spreadsheet using Index Collect when the Asset Name, Asset # and PM Type match. I have now been asked to make the formula work when the Asset #/Serial # column on the submission log matches EITHER the Asset # column OR the Serial # column on the master spreadsheet.
1st image is the Submission Log where users select asset #s OR Serial #s from a single drop down . 2nd image is the master spreadsheet with the 2 columns that contains this data for each piece of equipment. Some assets do not have serial #s and vice versa, so I need a formula that will work for either column if a match is found with the data in the "Asset/Serial#" column. I'm open to using helper columns or complex formulas if it prevents me from changing what an end user has to input on the electronic form.
Help Article Resources
Check out the Formula Handbook template!