Index Match or Index Collect ?

Nikki B

Hi yall. I'm not good with the formulas but need them to streamline my employee tracking process. I have a, "Source Sheet" with Employee Name, Class, Dept that need to (match or collect) into another sheet, "Training Log". What formula would I use? I am hung up on the second part of MATCH after you reference the column@row. Thanks!!

Mark.poole

there really isn’t a way to pull the name over. You could copy rows or do cell matching. How ever if you want it all automated that won’t work. You could put all the names you want to pull on the list manually and pull everything else. I don’t know how feasible that is with your data. The way an index works is it compares data on your current sheet to data on your reference sheet to pull the information. If name is your main identifier you have to enter it manually.

Mark.poole

=index({reference you want to pull},MATCH(column@row,{ref that matches the column@row}),0)

Nikki B

Thanks, Mark. What is the ref that matches the column? This is where I go blank…

=index({reference you want to pull},MATCH(column@row,{ref that matches the column@row}),0)

=index({SourceSheet}, MATCH (Employee Name@row, { can't figure this one out }),0)

Mark.poole

@Nikki B

In your source sheet. You should have all the employee names. the employee name your wanting should be on the same row as the information you are looking for.

=INDEX({SourceSheet}, MATCH([Employee Name]@row, {Employee Name Reference from source sheet}),0)

Nikki B

OK. I added the Employee Name reference from the Source Sheet as "DriverName". Error says "circuralreference"

=INDEX({SourceSheet}, MATCH([Employee Name]@row, {DriverName}), 0)

Mark.poole

Ok That means your formula is circling back on itself. Is it the employee name you are trying to pull?

Nikki B

Yes. Employee Name, Class, Dept (3 columns)

SourceSheet: (Columns 3, 4, 5)

Training Log: (Columns 1, 2, 3)

Mark.poole

ok you need some kind of identifier that’s in your source sheet that you are using to pull employee name over. What’s something I. Your training log that you manually enter. Or is pulled in by another formula that also matches something in your source sheet. That is not one of the three your trying to pull from your source sheet into your training g log

Nikki B

The name is the main identifier for me. Would another formula be better suited for this?

My goal is to have the Employee Name, Class, Dept populate on new sheets to track additional items (training and other items) not relevant to the Master Roster.

Mark.poole

there really isn’t a way to pull the name over. You could copy rows or do cell matching. How ever if you want it all automated that won’t work. You could put all the names you want to pull on the list manually and pull everything else. I don’t know how feasible that is with your data. The way an index works is it compares data on your current sheet to data on your reference sheet to pull the information. If name is your main identifier you have to enter it manually.

Nikki B

Thank you, Mark. I appreciate you taking the time!