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?

FLS Invoice Register.jpg

Find more posts tagged with

Formulas

Government

Comments

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.

FLS Asset Register.jpg

Paul Newcome

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

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…