Index Match Function Help

zbr

Hi Smartsheet Gurus,

I am attempt to pull information from one sheet to another given a set criterion that is present in both. I believe that either a VLOOKUP or INDEX function should suffice but cannot seem to get things to work.

A little background:

We would like to be able to have employees enter a form with data about the orders that they will work on. This form has a Created column that captures date and time which is considered the 'start time'. Ideally, another form connected to a second sheet would be used to collect an end time. These two values would then be subtracted from each other to get total time between (I already have functions for this set up).

A form submission on the first sheet looks something like this:

The second sheet that captures the end time looks like this:

Employees utilize the form on the first sheet to document up to six order numbers. I would like for the second sheet's form to just ask for the employee's name and to document any of the potential six order numbers to be used as a unique identifier between both sheets. The unique identifier would be queried across the potential six columns of order numbers on the first sheet and used to pull in an end time to the first sheet.

I have been able to get an INDEX(range, (MATCH(... to work on the second sheet. This obviously works as I am index the range of order numbers across the six columns against the unique identifier. I am wondering if there is a way to pull back the end time into the first sheet as that's where most of the data is captured.

Thank you!

Mike TV

@zbr

The way you have the first sheet set up, it looks like you're going to need to put an End Time column after every Order # column. So after "Order #1" column you'll need a column called something like "Order #1 End Time". Then the same for each order # on that sheet. So six of them. You'll need a formula in the "Order #1 End Time" column that's something like this:

=INDEX({Range 1}, MATCH([Order #1]@row, {Range 2}, 0))

The {Range 1} range should point to the entire "End Time HH:MM" column on sheet 2. The {Range 2} range should point to the entire "Order Number Identifier" column on sheet 2.

Then on Sheet 1 for the new column "Order #2 End Time" formula it would have this:

=INDEX({Range 1}, MATCH([Order #2]@row, {Range 2}, 0))

And so on for each of the 6 new columns for the End Times.

Mike TV

@zbr

The way you have the first sheet set up, it looks like you're going to need to put an End Time column after every Order # column. So after "Order #1" column you'll need a column called something like "Order #1 End Time". Then the same for each order # on that sheet. So six of them. You'll need a formula in the "Order #1 End Time" column that's something like this:

=INDEX({Range 1}, MATCH([Order #1]@row, {Range 2}, 0))

The {Range 1} range should point to the entire "End Time HH:MM" column on sheet 2. The {Range 2} range should point to the entire "Order Number Identifier" column on sheet 2.

Then on Sheet 1 for the new column "Order #2 End Time" formula it would have this:

=INDEX({Range 1}, MATCH([Order #2]@row, {Range 2}, 0))

And so on for each of the 6 new columns for the End Times.

zbr

This did the trick! I then utilize an INDEX COLLECT combo to pull out the nonblank cell to display the end time. Here's what I use in case it might help anyone:

=IFERROR(INDEX(COLLECT([Order #1]@row:[Order #6]@row, [Order #1]@row:[Order #6]@row, @cell <> ""), 1, 1), "In Progress")