1
I have a table which contains multiple references per cell separated by a blank space.
In an other table that contains one line per reference, I want to use VLOOKUP to retrieve the name of the component which is linked to each reference.
Here is an example of my first table:
And here is the result I'm trying to achieve:
Use VLOOKUP of the reference to get the component name in the first table.
I cannot get it to work as the number of references associated to a component is not constant.
EDIT:
I came up with a workaround with partial wildcard matching that has some limitations:
VLOOKUP("*" & (table2 Ref) & "*", table1, 2, FALSE)
The limitations are:
- I have collisions when my references are for example A011 and A01, searching for A01 will give the result of A011 if the reference of A011 is higher in the table.
- VLOOKUP doesn't work if the cell contains more that 256 characters. With my 30 references per cell at most and each references can have up to 10 characters (space included) I have cells with more than 256 characters.
This workaround has made me to split up the lines in table 1 so that the reference column has less than 256 char and to adapt the reference convention to ensure that there are no collisions (encore all the reference in 10 characters, filling with 0 when needed). As the table 1 is more or less an extract of another software and the naming convention that is not an ideal solution for maintaining the file but it works for now. Any other solution would be welcomed.
What does table 2 look like? I would start by trying to split out the Reference text into multiple cells: https://support.office.com/en-us/article/Split-text-into-different-cells-30B14928-5550-41F5-97CA-7A3E9C363ED7
– mcalex – 2016-08-18T09:16:11.723The result is what table 2 looks like. In fact I have (up to now) around 100 assemblies, 1000 components with between 1 and 30 references each that's why I don't want to split up the cells. – AdrienNK – 2016-08-18T09:20:30.453
Do all reference records in the 2nd table already exist and you're just looking up the component name from the 1st table, or are you trying to build the 2nd table from scratch based on the 1st table? – fixer1234 – 2016-08-18T16:10:24.183
Sorry that wasn't clear. I'm manually writing that A01, B01 and A02 are parts of P0024 and I want to retrieve the component names (and other stuff too but that will be using the same formula). I have an preliminary workaround that I explain in the question. – AdrienNK – 2016-08-19T08:18:48.057
You may try using array formulas, they may don't have same limitations as VLOOKUP, however performance can be slower; or use macros. But honestly, the true solution I think is to change data structure / tool. – Máté Juhász – 2016-08-19T12:40:24.763
Still not clear. You show table 2 "after". What does it look like "before". – fixer1234 – 2016-08-20T05:09:25.170