Lookup values in a multiline cell

[srinidhi]

I need to lookup values from a cell to another Multiline cell.
ex:
A B
ID Name
001 ARUN
OO2 Pawan
003 Sunil

In C, I the ID, but the ids are in a single cell.
C2 has ID number 001 & C3 has Id number 002 & 003 one below the other.
In D, I want to vlookup for ID & Name from A & B . D3 should have both Pawan & S

 

[Xiq]

I find your question very confusing
What is actually the input you want to look-up and what should be the result?

 

[srinidhi]

Hi Xiq, please find attached the excel file.

 

[Xiq]

Place this formula in E2 and copy down:

=IFERROR(LOOKUP(CLEAN(LEFT(D2,FIND(CHAR(10),D2)-1)),TEXT($A$2:$A$4,"#"),$B$2:$B$4)&" & "&LOOKUP(CLEAN(MID(D2,FIND(CHAR(10),D2),3)),TEXT($A$2:$A$4,"#"),$B$2:$B$4),VLOOKUP(D2,$A$2:$B$4,2,0))

Does this work for you?

 

[Sajan]

Hello srinidhi,
How many IDs can a cell in column C contain? 1, 2, 3, or more?

If you are not able to restructure your data (putting the values from a single cell into multiple cells), and you may have an unknown number of IDs packed into a single cell, a VBA solution is what you will need to keep the solution uncomplicated.

However, if you will have up to 2 IDs in a cell, then Xiq has offered you a solution.

 

[srinidhi]

Thanks Xiq you formula works. Is it possible to get the result data one below the other instead of the data with &.

Sajan, Cell C has more than 5 data & the data is huge upto 5000+ rows.

 

[Xiq]

I guess replacing the <" & "> with <CHAR(10)> should do the trick. Like this:

=IFERROR(LOOKUP(CLEAN(LEFT(D2,FIND(CHAR(10),D2)-1)),TEXT($A$2:$A$4,"#"),$B$2:$B$4)&CHAR(10)&LOOKUP(CLEAN(MID(D2,FIND(CHAR(10),D2),3)),TEXT($A$2:$A$4,"#"),$B$2:$B$4),VLOOKUP(D2,$A$2:$B$4,2,0))

 

[Xiq]

Sajan, Cell C has more than 5 data & the data is huge upto 5000+ rows.

Keep in mind, my formula can only handle 1 or 2 ID's in the same cell (like Sajan mentioned).

 

[srinidhi]

Thanks Xiq, will get back to the forum if I am not able to code the VBA Part