Index/Match Contains?

Kheer

Hi All,

Relatively new to smartsheets so thanks in advance for assistance on this!

I am working on a smartsheet that will help organize our employee jobs & pay (some of our employees work two jobs at our university setting). I have a report that I can run from our payroll system that I export to excel and then import to smartsheets. This report includes their names, University ID, Job Title, and Pay (as respective column names)

The problem is when I import the report into smartsheets, if employees have multiple job titles (like "researcher" and "professor") I cannot use index/match when pulling from this excel spreadsheet import, to the smartsheet I am working on called "Researcher Payrolls." My goal is monthly to run the report from my university payroll system, import that report to smartsheet, and then have a formula find by searching the employees university ID, search for the jobs that just contain "researcher." I feel like I would probably need to use some sort of INDEX searching the ID # and then CONTAINS "Researcher" in some fashion, but keep getting stuck. any help is super appreciated!

Seth Morth

Hello @Kheer!

I have some examples of what I have done below. I am not clear if you are trying to do this all on one Sheet or if there are multiple Sheets involved. Note that the stuff in brackets {..} are references to other sheets and are created by clicking this link Reference Another Sheet when it appears during formula entry (you can prompt it to appear by typing in an '{' but you'll wind up with an extra '{' that you will need to remove)

CONTAINS...***THE EXAMPLE BELOW WORKS IF THE STOCK CODE FIELD Contains 'SCON01' as PART of the TEXT, BUT NOT THE WHOLE TEXT IN THAT CEL*** 

=IFERROR(SUMIFS({PO Detail_ReceivedValueInYear}, {PO Detail_StockCode}, CONTAINS("SCON01", @cell)), "")

COLLECT...***THIS IS A BUSY EXAMPLE, IT DOES A LOT; Sums up the $ total of any orders based on Supplier Name (exact match), and meets the specified month (1=January) of the current year*** 

=SUM(DISTINCT(COLLECT(OrderTotalCost:OrderTotalCost, SupplierName:SupplierName, ="Office Max", OrderEntryDate:OrderEntryDate, AND(MONTH(@cell) = 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))))

HAS...***THE EXAMPLE BELOW IS PULLING IN WHATEVER IS IN THAT CEL, YOU CAN TOSS IN "AND" AND/OR "OR" TO HELP OUT; This one looks up a PO# and a PO Line# on Sheet-A and pulls in whether or not on Sheet-A how many have been received and displays it on Sheet-B (Where this formula lives)***

=IFERROR(INDEX(COLLECT({CGC-Purchase_Received}, {CGC-Purchase_PO}, HAS(@cell, [PurchaseOrder]@row), {}, HAS(@cell, [PO Line]@row)), 1), "Not Found")

I hope this helps!

• Seth