My company has created a freelancer invoicing system using two sheets. It depends on an INDEX formula that we now need to improve, which I'm having trouble doing.
- Sheet #1: A database with information on all individual freelancer (aka subcontractors) contracts (aka SOWs)
- Sheet #2: A form-based tracker with notifications that are sent to freelancers based on how their submitted invoices match up to their contracts
When an invoice is submitted to Sheet #2, the freelancer writes their name, email address , and an SOW (contract) code on the form. Sheet #2 then uses an IF ERROR/INDEX formula (written by a professional outside the company) to match the invoice to a contract/SOW in Sheet #1 using those criteria. However, this system isn't perfect; sometimes the freelancers will use minor variations of their names that prevent the formula from matching it directly to the corresponding name in Sheet #1.
We would like to update this formula so that it only matches using the email address and SOW code, NOT the name. However, I haven't had any success trying to update the formula by myself. I thought this would be a simple matter of removing the "Sub Contractor Name" but no luck. Any suggestions would be appreciated!
Current formula:
=IFERROR(INDEX(COLLECT({Subcon Email}, {SOW Code}, [SOW #]@row , {Sub Contractor Name}, [Subcontractor Name]@row ), 1), "")
{Subcon Email} = Sheet #1
{SOW Code} = Sheet #1
{Sub Contractor Name} = Sheet #1
[SOW #]@row = Sheet #2
[Subcontractor Name]@row = Sheet #2
