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.