How to Index Match

Tamsyn

Hi there,

Please help, I am not great at formulas!

I have a source Employee file that has all the information I need.

I want to add information from the Employee Info sheet to a training register on a different sheet.

There are several column items I want to bring into my sheet e.g. ID number, Job Title, etc

I have added a row with headers to reference in the formula.

I want to match the data between the two via the Smartsheet contact or "Consultant" name.

I have used the formula:

=INDEX({Employee Sheet Range 2}, MATCH(Consultant@row, {Employees Range 3}, 0), MATCH("ID No",{Employees Range 4}, 0))

Where Range 2 = all the selected columns e.g. ID, Job Title, Consultant

Where Range 3 = Employee Email/ Consultant column

Where Range 4 - Employee ID number column

When I pull through this it shows the ID number for the relevant person

However when I try to use the same formula in another column and change the Range 4 it changes the answer in the other column to the same value.

Anyone know how to resolve/ get this right?

Paul Newcome

@Tamsyn Happy to help. 👍️

Something to note:

I frequently use INDEX/COLLECT as @sharkasits mentioned, but there are times where an INDEX/MATCH/MATCH as you have is in fact appropriate.

INDEX/COLLECT is great if you are pulling from a single column but need to match on multiple columns.

INDEX/MATCH/MATCH is more for referencing an entire table where you need to match on both the horizontal and vertical axis.

INDEX/MATCH is for pulling from a single column (or row) while also matching on a single column (or row).

INDEX/MATCH/MATCH: You want to be able to pull from every row in columns 16 through 19 based on a value in Column 15 as well as a value across the top row.

=INDEX({Columns 16-19}, MATCH(2, {Column 15}, 0), MATCH("B", {Row 1}, 0))

INDEX/COLLECT: You want to pull from column 7 based on a match in both columns 15 and 16.

=INDEX(COLLECT({Column 17}, {Column 15}, @cell = 3, {Column 16}, @cell = "B"), 1)

INDEX/MATCH: Similar to INDEX/COLLECT but you only want to match on a single column.

=INDEX({Column 17}, MATCH(3, {Column 15}, 0))

INDEX/MATCH across a row: You can also use INDEX/MATCH to pull from across a single row and use the MATCH in the third portion of the INDEX function. Similar to INDEX/MATCH/MATCH, but the first MATCH is replaced with a 1 (row 1) since there is only 1 row being referenced.

=INDEX({Row 2}, 1, MATCH("C", {Row 1}, 0)

Paul Newcome

In the second formula, make sure you highlight the entire range including the curly brackets so that the blue link in the formula help box changes from "Edit Reference" to "Reference Another Sheet" so that you can create a new cross sheet reference as opposed to updating the existing one to a different range.

sharkasits

@Tamsyn A few questions...

1. What information is entered on the training register that you are using to tie to the Employee Sheet?
2. Does the Employee Sheet have potential duplicates on the field you're using to tie to?
3. Are the headers you added in the Employee Sheet? And is this because the column order or Names might change?

If your answers are what I think they will be... you don't need the header columns, since an INDEX(COLLECT()) will keep the reference to the right column even if the name or position changes.

=INDEX(COLLECT({<<Uniquely Named Range of column you are trying to pull in>>}, {<<Range of column you are tying to>>}, <<column in sheet you are tying to>>@row),1)

As for the the initial value changing when you change Range 4, references are defined at the sheet level, so I'm assuming you are trying to keep it as Range 4 but reference a different column. You need to have a different name for each reference.

If I assumed the wrong answers, let me know and I can help come up with another solution.

Tamsyn

Thank you so much @Paul Newcome - I did not know that each new reference needed to be a new range. I selected the {} and amended and it brought through the unique data.

@sharkasits thank you for the tip for COLLECT - I will try this as it seems a safer way in ensuring formula's don't get broken.

Paul Newcome

@Tamsyn Happy to help. 👍️

Something to note:

I frequently use INDEX/COLLECT as @sharkasits mentioned, but there are times where an INDEX/MATCH/MATCH as you have is in fact appropriate.

INDEX/COLLECT is great if you are pulling from a single column but need to match on multiple columns.

INDEX/MATCH/MATCH is more for referencing an entire table where you need to match on both the horizontal and vertical axis.

INDEX/MATCH is for pulling from a single column (or row) while also matching on a single column (or row).

INDEX/MATCH/MATCH: You want to be able to pull from every row in columns 16 through 19 based on a value in Column 15 as well as a value across the top row.

=INDEX({Columns 16-19}, MATCH(2, {Column 15}, 0), MATCH("B", {Row 1}, 0))

INDEX/COLLECT: You want to pull from column 7 based on a match in both columns 15 and 16.

=INDEX(COLLECT({Column 17}, {Column 15}, @cell = 3, {Column 16}, @cell = "B"), 1)

INDEX/MATCH: Similar to INDEX/COLLECT but you only want to match on a single column.

=INDEX({Column 17}, MATCH(3, {Column 15}, 0))

INDEX/MATCH across a row: You can also use INDEX/MATCH to pull from across a single row and use the MATCH in the third portion of the INDEX function. Similar to INDEX/MATCH/MATCH, but the first MATCH is replaced with a 1 (row 1) since there is only 1 row being referenced.

=INDEX({Row 2}, 1, MATCH("C", {Row 1}, 0)

Tamsyn

@Paul Newcome this is so helpful thank you very much!

Paul Newcome

Sure thing! I know they have some basic documentation on the INDEX/MATCH, but I don't think they have something that really covers each of the variations.

melgilkessmith

@Paul Newcome I am attempting a similar formula but I keep getting a return value of 1. Here is my formula.

=INDEX({ReferralReport_Test Range 1}, MATCH(3236, DoctorFacilityId:DoctorFacilityId, 0), MATCH("Jan 2024", {ReferralReport_Test Range 2}, 0))

ReferralReport_Test Range 1 = all of the columns like you stated above 16-19

ReferralReport_Test Range 2 = the 1st row in the other sheet

I have another sheet that has the actual number of referrals seen each month based on the DoctorFacilityId. I'm testing the formula this way and hoping to use it as an expression for Data shuttle so that each time I upload an Excel sheet through Data Shuttle it will update the columns based on matching the DoctorFacilityId.

Paul Newcome

@melgilkessmith I would need to also see your source data.

melgilkessmith

@Paul Newcome it would look like this in Excel

Paul Newcome

@melgilkessmith Ok, but what does the Smartsheet look like, and what exactly are the different {Cross Sheet References} looking at?

melgilkessmith

@Paul Newcome the Smartsheet is up above. It is just the one sheet. I would like for the INDEX/MATCH to happen between the excel file and the sheet via data shuttle. Is that possible with a Data Shuttle expression?

Paul Newcome

I'm not sure I follow. What are your {Cross Sheet References} pointing at?

melgilkessmith

@Paul Newcome I may have explained that poorly. I only have one sheet and one excel. When I upload the excel sheet into data shuttle. I want Data Shuttle to lookup the DoctorFacilityID in the sheet, match it with the ID on the excel and return the corresponding value in the month column at that ID row. I also want data shuttle to add any rows where there are new ID's in the source sheet.

I can't figure out the formula to do that.

Paul Newcome

You shouldn't need any formulas in Data Shuttle for that. Data Shuttle is already programmed to match on a unique ID when you are doing the mapping portion of the workflow build.

mrsci3ntist

Hi @Paul Newcome,

Similar question, but here my extended question is how to make the following into a column formula ? (Note this is a screenshot of a sheet from within Smartsheet's "Project Management Office" template workspace). The issue lies with the [column_index] always matching to the first row which will not work in a column formula.

Thanks for your help!