How to convert a list of numbers into a list of names?

kdrinkwater

I have a data set that includes numerical IDs. The ID column can, and often does, contain a list of IDs separated by commas. Like this:

ID
5
71
53
7
10,16,34,35,37,40
60,61

The IDs themselves correspond to Names but the system the data is exported from does not have the Names. I would like to do a VLOOKUP of the IDs to return the names so that the final output looks like this:

ID	Name
5	Alligator
71	Bear
53	Cat
7	Dog
10,16,34,35,37,40	Elephant, Flamingo, Gorilla, Hippo, Iguana, Jaguar
60,61	Koala, Leopard

I expect that I'll need to use CONTAINS nested in an IF statement to identify when the ID column contains a list, but after that point I don't really know where to begin to look up each ID and return a Name, then recompile into a list of names. I'm thinking of using REPLACE as well, but I don't know if that will let me move through a list until all IDs have been replaced with names.

Any ideas?

Brian_Richardson

If your lookup sheet has a list of IDs and Names, one per row, then you can use this formula:

= JOIN( COLLECT( {Name}, {ID}, CONTAINS( @cell, ID@row)), ",")

What this is doing:

• One row at a time on your lookup sheet, COLLECT is getting the ID and then seeing if the ID column on your data set sheet CONTAINS that ID. Essentially kindof a "reverse lookup" back to your original data set.
• If the ID column in your data set contains the first lookup row's ID number, then COLLECT adds the Name from that lookup row to a temporary array.
• COLLECT then moves to the next row on the lookup sheet, then next, etc etc through the entire sheet.
• Then JOIN takes the temporary array of matching results and separates them with a , and puts the data into your Name cell on the data set sheet.

The {Name} reference points to the Name column on your lookup sheet. You add those to your formula by clicking "Reference Another Sheet" in the popup formula helper box as you type out the formula, then browsing to the lookup sheet and selecting the Name column header to highlight the column. In the top box of the browser, give it the name "Name" and hit Insert Reference. Repeat for the {ID} reference.

Brian_Richardson

If your lookup sheet has a list of IDs and Names, one per row, then you can use this formula:

= JOIN( COLLECT( {Name}, {ID}, CONTAINS( @cell, ID@row)), ",")

What this is doing:

• One row at a time on your lookup sheet, COLLECT is getting the ID and then seeing if the ID column on your data set sheet CONTAINS that ID. Essentially kindof a "reverse lookup" back to your original data set.
• If the ID column in your data set contains the first lookup row's ID number, then COLLECT adds the Name from that lookup row to a temporary array.
• COLLECT then moves to the next row on the lookup sheet, then next, etc etc through the entire sheet.
• Then JOIN takes the temporary array of matching results and separates them with a , and puts the data into your Name cell on the data set sheet.

The {Name} reference points to the Name column on your lookup sheet. You add those to your formula by clicking "Reference Another Sheet" in the popup formula helper box as you type out the formula, then browsing to the lookup sheet and selecting the Name column header to highlight the column. In the top box of the browser, give it the name "Name" and hit Insert Reference. Repeat for the {ID} reference.

kdrinkwater

Thank you, @Brian_Richardson this worked perfectly. My lookup sheet was a list of IDs and Names, one per row, as you described. Really appreciate the bullets explaining what the formula was doing. I've never had a use case for COLLECT or JOIN before, which explains why I was in the dark.

Since I had numerical values from 0-1000, I was getting false positive hits on partial matches (ex. When 10 was in the ID list, the Name that corresponded to 1 was being listed). I swapped out the CONTAINS for a HAS after adding a multiselect, helper column.

My final formulas were:

For the helper column required to use the HAS function:

=SUBSTITUTE([ID]@row, ",", CHAR(10))

For creating the list of names:

=JOIN(COLLECT({Names}, {ID}, HAS(cleanID@row, @cell)), ",")