Vlookup data across sheets

Ranjini Walter

Hi

looking for ways where the manager's data can auto-populate if only they enter the name

So if Andy enters is name he should be able to see the dept. and sub-dept. populate in the columns.

Need help in understanding this concept.

Hannah H

Hi @Ranjini Walter,

My recommendation is to use an INDEX/MATCH formula. You will need a source sheet that contains the employee names and their departments and sub-departments.

The formula would look something like this:

=INDEX({department column in source sheet}, MATCH([Manager Name]@row, {employee name column in source sheet}, 0))

What is happening here is that the INDEX is telling Smartsheet what data to pull in and the MATCH is telling Smartsheet what info to look up and cross-reference.

This is more stable than using a VLOOKUP; if you move a column position in your source sheet a VLOOKUP will break. INDEX/MATCH is stable and not affected by changes to your source sheet.

Best,

Hannah

Hannah H

Hi @Ranjini Walter,

My recommendation is to use an INDEX/MATCH formula. You will need a source sheet that contains the employee names and their departments and sub-departments.

The formula would look something like this:

=INDEX({department column in source sheet}, MATCH([Manager Name]@row, {employee name column in source sheet}, 0))

What is happening here is that the INDEX is telling Smartsheet what data to pull in and the MATCH is telling Smartsheet what info to look up and cross-reference.

This is more stable than using a VLOOKUP; if you move a column position in your source sheet a VLOOKUP will break. INDEX/MATCH is stable and not affected by changes to your source sheet.

Best,

Hannah

Ranjini Walter

Hi Hannah

Thank you for the solution. Will try the formula and touch base if I still don't get it.

Have a great day!

Thanks

Ranjini Water

Ranjini Walter

Hi Hannah....The formula worked but I have one question regarding the column properties for the employee column:

If I change the column properties to contact list the formula doesn't work, Could you pls explain this to me?

Thanks

Ranjini Walter