Excel: VLOOKUP with cell containing multiple references

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:

enter image description here

And here is the result I'm trying to achieve:

enter image description here

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:

  1. 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.
  2. 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.

AdrienNK

Posted 2016-08-18T08:51:57.963

Reputation: 123

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.723

The 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

No answers