Index only returning results if at the top of the source sheet

Ev Morris

Hi All,

Hoping someone can help me please?

I am using the formula below but it will only work if the search value, System ID, is in the top rows of the reference sheet.

=IF(INDEX({Reference Range System ID}, [Row ID]@row) = [System ID]@row, INDEX({Equipment ID}, [Row ID]@row))

What I am trying to do is, If the System ID in the reference sheet = the System ID in the sheet I am working on, pull in the Equipment ID from the reference sheet.

There could be multiple rows with information for the same System ID, that's why I added an auto number Row ID column to the sheet I am pulling the information into.

Works perfect for System ID 1 but goes blank if I update to 2

Does someone know why it won't look past the first set of data in the reference sheet?

TIA!

Ev Morris

I've just woken up and figured out the result is going to be pulled in relation to the Row ID# so if I only have Row ID up to, lets say 150 in the sheet I am working on, it will only pull the information up to that Row# in the reference sheet.

So now my question is, if the reference sheet has 100's of rows and can update all the time, how do I pull in this information without reference to Row ID?

I've used Index Match but am only getting the 1st value from the source list.

Aya

Hi Ev,

Have you tried Index/Match with multiple criteria?

You can achieve this by creating a helper column with a JOIN() formula so you can look up using System ID and Row ID. Just make sure to put the helper column on both sheets so you can use it as the search_value on the main sheet and reference column on the reference sheet.

Also, you can hide these helper columns to save viewing space.

Helper Column Formula: =JOIN([System ID.]@row:[Row ID]@row)

System ID 1

System ID 2

Mock Reference Sheet

Hope this helps. 🙂

Ev Morris

Thanks @Aya!

I did figure this out, but needed more than identified in your solution because:

• The source data has 1000's of rows so couldn't list all row IDs in my working file, needed for Index to work
• New data might be added to the source file, but not sorted
• Could have data in rows 1,2, and 3. And another item in line e.g. 650.

My solution has a lot going on, and there probably is a lot easier way to do it, but it works!

1st I needed to add 4 helper columns to the source file:

1. Count - Count the number of times a System ID appears in the data set - =IF(ISBLANK([SystemID]@row), "", COUNTIF(System ID:SystemID, SystemID@row))
2. Sequential ID - A sequential number assigned to each SystemID, each time it appears in the data set. E.g. If 5 instances of System ID#1, each line item will be assigned 1, 2, 3, 4, or 5 in order. This was the only formula I needed to drag down, column formula not allowed - =IF(ISBLANK(SystemID@row), "", COUNTIFS(SystemID$1:SystemID@row, SystemID@row, Count$1:Count@row, Count@row))
3. Row ID - A unique ID that joins the System ID with the Sequential ID - =(SystemID@row + "-" + [Sequential ID]@row)
4. A Row Index Number - This is essential where if a row is added/moved etc, it will update to show the true Row Number - =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

And then added the following helpers to the working file:

• System ID# - This is just the original System ID copied to each row (column formula) - ="SystemID-1"
• Count Line Items for System ID - A count of how many times the system ID appears in the source file - =COUNTIF({Source File SystemID}, [SystemID#]@row)
• Row ID - Sequential number in each row starting at 1 - This is Manual. I thought about making this a Auto Number column but I need to drag this down to ensure the total Count result from above is identified
• Unique System ID - Creating the unique ID here - =[SystemID#]@row + "-" + [Row ID]@row
• Row Index Number - This is a VlookUp to bring in the Row Index identified in the source file, I couldn't use an Index Match as This is the value I need for other Index formulas in the sheet - =IFERROR(VLOOKUP([SystemID]@row, {Source Data Range from The Unique Row ID to The Row Index Number}, 2, 0), "")

Now I have everything I need to create Index formulas for all other columns where I need to drag in the information from the source file. All I must do is ensure the Row ID number in the working file identifies each individual number up to and including, or more than, the result from the "Count Line Items for System ID" formula. In the formula below, MD = Master/Source Data.

=IF(ISBLANK([Row Index MD]@row), "", IFERROR(IF(INDEX({MD Unique System ID Value}, [Row Index MD]@row) = [SystemID]@row, INDEX({MD Data I want to bring in}, [Row Index MD]@row)), ""))

Hope this helps anyone finding the same issue. I do prefer to put the effort in to set up for Index formulas as VlookUp's can break if any changes are made to reference columns.

If anyone has an easier way to do this please do share!

Ev Morris

Adding a link to a solution I found from @Paul Newcome https://community.smartsheet.com/discussion/31256/number-of-duplicates

This allowed me to make the manual "Sequential ID" formula above, a column formula, making things so much better!