CONTAINS, MATCH, INDEX Help

miranda.sandi.PDX

I'm trying to use CONTAINS, MATCH, and INDEX in cross-sheet formulas to pull location-specific system component inspection information from invoices to my asset register.

Sometimes, the invoices will have more than one system component on them.

I want to make sure the formulas will pull the information for each of the system components. In the attached screenshot on line 11, I would get the result "2/28/19" for both 01-Jan and 02-Feb Extinguishers.

I've created a column that concatenates the location and system component but I'm not sure that's going to work for the invoices that have multiple components.

Any thoughts?

Paul Newcome

What does your target sheet look like? Can you provide a screenshot of that as well and possibly even manually enter data so that it mimics what you want to automate?

miranda.sandi.PDX

Sure. Below is a screenshot of my asset register. I want to pull the [Invoice Date] from my FLS Invoice Register into the [Last Inspection] column.

As you can see, each site has several systems and inspections can be combined. 

If I did this manually, I would switch back and forth between the Invoice and Asset register updating the [Last Inspection] column as invoices come in.

 

 

Paul Newcome

Ok. So just to be clear... You want to look at location and component and pull the most recent date?