Index/Match

KTM

I have two sheets that are capturing two different sets of data.

Sheet 1 (Billable Mileage Log) is capturing projects that have been marked as needing mileage to be entered in a different platform.

Sheet 2 is capturing mileage that's being entered via a Smartsheet form. On this sheet, I have two columns that have formulas.

I am using the data from Sheet 1 and Sheet 2 in a report to identify which projects need mileage applied to them, and how much mileage needs to be applied.

Mileage Match on JRS column formula is:

=IFERROR(INDEX({Billable Mileage Log Range 1}, MATCH([Project # or Quote #]@row , {Billable Mileage Log Range 2}, 0)), "DO NOT EDIT")

Project # Match column formula is:

=INDEX({Billable Mileage Log Range 2}, MATCH([Project # or Quote #]@row , {Billable Mileage Log Range 2}, 0))

Issue I am trying to resolve:

My team is wanting to continue entering multiple project numbers on sheet 2, but my formulas cannot match unless it's an exact match from sheet 1. Sheet 1 will only ever have one project number.

Using the last row of data on my screenshot above as an example:

Sheet 1 has 0000-0000 on one row, and 1111-1111 on another separate row.

Is there a formula or different work around that can make this work, or will I have to tell my team that they need to do separate entries for each project number on sheet 2?

Thanks in advance for any suggestions!

Michelle Choate 2

Have you tried using the LEFT() formula for another column to pull out the just the project number that is in first? But in that case it would only produce the first Project#, not the second. IDEALLY they would be separate rows.