vlookup/index across 2 different sheets

Hi everyone,

I am trying to build out a workflow between two smart sheets - Our end goal is to have a user select from a drop-down list of numbers which once selected will then populate across the columns in the sheet. The sheet with the master data it would be referencing has the same column headers, the challenge I am running into is I need to reference 7 different columns to populate this out. The payroll number column header is the drop down. Thank you in advance!


Tags:

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @alicia151

    I would use a index match formula, you will need 7 of them but it will minimise the amount of cells being processed and is cleaner to run than vlookup, also if you ever change the master sheet, add a column etc. it wont break the match like it could with a vlookup.

    Below is a example based on your screenshot, this is the correct format but wont work pasted into your sheet straight away as you need to create the ranges


    =INDEX({Last name range 1},MATCH([Payroll Number]@row,{Payroll number Range 1},0))

    once you create the first formula you can then use it for the other 7 columns, you just need to change the index range '{Last name range 1}' in the example above to be one of the other columns in your master sheet, i.e. {Position range 1} etc.

    Hope that helps

    Thanks

    Paul

  • alicia151
    alicia151
    edited 01/12/23

    Hi Paul, thanks for that reply, I'll input it now and let you know :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!