INDEX(MATCH)?

Options

I have two sheets. I would like to do a formula similar to index/match but I would like to pull in multiple strings. The index formula is =INDEX({Asset-DeviceSheet}, MATCH([Employee ID]@row, {eNumber-HPLStaffAssignedDevices}, 0), 0)

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭
    Options

    @chenice.howard81116 Can you explain what you mean by "pull in multiple strings"? Do you mean you want to concatenate several columns from a source sheet into a single column in a target sheet?

    Otherwise, that first range {Asset-DeviceSheet} can be several columns wide. The last digit - the last zero in your case - indicates which column from that range to place into the target column. So if you copy that formula into several columns and change that column-value you can reference several columns.

    dm

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭
    Options

    I'm interested in this thread as I'm trying to learn more about INDEX and MATCH and how/why I would use them over my comfortable VLOOKUP function. Are you @chenice.howard81116 trying to return device counts or unique device IDs for the Employee ID in each row?

  • chenice.howard81116
    edited 11/22/21
    Options

    Hi,

    I was able to figure out the formula. I was trying to return the devices assigned to all staff members based on their employee id #. Sheet A is the onboarding sheet. Sheet B is the technology master sheet. Sheet B is the device assignment sheet. Information from Sheet A and C are referenced in Sheet B. I've used the following formula in SheetB:

    =JOIN(COLLECT({dev@SheetC}, {enumb@SheetC}, =[Employee ID]@row), ", ")




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!