replace name with id by lookup value from list

[itmanager]

Hi,

It's something similar to this http://www.excelforum.com/excel-prog...in-a-list.html

but in my case the worksheet 1 column B have comma separated values which need to be looked up in worksheet 2 column B and replace it using the associated id in column A of worksheet 2.

Please see attached.

Any help much appreciated!

Thank you.

[sourabhg98]

What would be the ID for "A,E,L"...
Maybe...1,5,12??

[itmanager]

yes that's correct.

But please keep in mind its just a sample file. My actual sheet 1 will be having about 25,000 records and for each record column B cell will have about 50 comma separated values and sheet 2 data is having about 80,000 records to be looked up.

Thanks for your prompt reply.

[sourabhg98]

Hi
This is quite complicated...
Although I have tried my best according to my knowledge...
The only limitation I think is that you need to build the concatenate formula in column C for 50 values as you said there may be up to 50 values....
Give it a try...

[itmanager]

Hi sourabhg98,

Thanks your solution helps. and I can do concatenate but is there a way to put first and last cell in concatenate formula and it includes all in between or I have to list all cells.


Thank you.

[sourabhg98]

Yes there is a User Defined Formula....
But it may slow down your sheet when you apply it to thousands of rows....
But still..give it a try..