vlookup/index across 2 different sheets

alicia151

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!

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

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