INDEX(MATCH)?

chenice.howard81116

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)

Dale Murphy

@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

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

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), ", ")