How can I use Index Match Match

sstelmack

I am trying to use index match to pull a contact name from the last person who worked on the project.

This one works which is on the same page but I need a version from a different page

=INDEX([ASSIGNED ENGINEER]@row, MATCH([JOB NAME]@row, ""), MATCH([JOB NAME]@row, ""))

Im following the same rules index the Range of people and match the same Columns to pull any name that is there

=INDEX({APN/DFA COMPLETED Range 3}, MATCH({APN/DFA COMPLETED Range 2}, ""), MATCH({APN/DFA COMPLETED Range 2}), "")

I also tried this using the job name on the page im currently on

=INDEX({APN/DFA COMPLETED Range 3}, MATCH({APN/DFA COMPLETED Range 2}, ""), MATCH([JOB NAME]@row), ""))

heyjay

You are missing the last Match arguement, zero for exact match.

…MATCH([JOB NAME]@row, "",0)…

sstelmack

So like this

=INDEX({APN/DFA COMPLETED Range 3}, MATCH({APN/DFA COMPLETED Range 2{APN/DFA COMPLETED Range 4}}, ""), MATCH([JOB NAME]@row), "", 0 ))

that does not work

heyjay

Paul N made a suggestion when matching multiple criteria. Use Index Collect instead:

How to Index Match

Tamsyn

Jan 27, 2023

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

https://us.v-cdn.net/6031209/uploads/VUXTZMOYK8XW/image.png

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?

---

View Post

sstelmack

I did try that as well,I want it to pull a name from the same JOB name

=INDEX(COLLECT([ASSIGNED ENGINEER]@row, [JOB NAME]@row, "", [JOB NAME]@row, ""), 1)

=INDEX(COLLECT({APN/DFA COMPLETED Range 3}, [JOB NAME]@row, "", {APN/DFA COMPLETED Range 4,""}), 1)