Look up Number in a list and return matching text

[kishorenaidugurram]

Lookup.xlsx
	I	J	K	L	M	N
2	To be Looked into	Output			Name	ID
3	ABC(1234),FGH(8976),EFG(4567)	ABC	Looking or a Formula in J3		ABC	1234
4	FGH(8976),EFG(45678),CDE(3456)	CDE	Looking or a Formula in J4		BCD	2345
5					CDE	3456
6
7	Need to look values in Col N in Col I and if matching found return the associated name
8	Ex: 1234 is in Cell I3 and return corresponding text i.e, ABC
Sheet2

 

[kishorenaidugurram]

I also included a VBA solution in case he didn't have 365.

@Irobbo314.. thanks for your efforts. I will come back once i test the VBA code

 

[kishorenaidugurram]

@Irobbo314.. thanks for your efforts. I will come back once i test the VBA code

Can we also please try with an excel formula.

 

[kishorenaidugurram]

I also included a VBA solution in case he didn't have 365.

Can we also please try with an excel formula.

 

[kweaver]

I also included a VBA solution in case he didn't have 365.

Right you are. Good function!

 

[kweaver]

Pretty ugly in J2 and filled down:

=RIGHT(LEFT(","&I2,SEARCH(N2,I2)-1),LEN(LEFT(","&I2,SEARCH(N2,I2)-1))-FIND("@",SUBSTITUTE(LEFT(","&I2,SEARCH(N2,I2)-1),",","@",LEN(LEFT(","&I2,SEARCH(N2,I2)-1))-LEN(SUBSTITUTE(LEFT(","&I2,SEARCH(N2,I2)-1),",",""))),1))

 

[Peter_SSs]

Perhaps I am interpreting the question incorrectly, but what about this?

21 05 10.xlsm
	I	J	K	L	M	N
1
2	To be Looked into	Output			Name	ID
3	ABC(1234),FGH(8976),EFG(4567)	ABC	Looking or a Formula in J3		ABC	1234
4	FGH(8976),EFG(45678),CDE(3456)	CDE	Looking or a Formula in J4		BCD	2345
5					CDE	3456
Sheet2 (2)

Cell Formulas
Range	Formula
J3:J4	J3	=LOOKUP(9^9,FIND("("&N$3:N$5&")",I3),M$3:M$5)

 

[kishorenaidugurram]

Perhaps I am interpreting the question incorrectly, but what about this?

21 05 10.xlsm
	I	J	K	L	M	N
1
2	To be Looked into	Output			Name	ID
3	ABC(1234),FGH(8976),EFG(4567)	ABC	Looking or a Formula in J3		ABC	1234
4	FGH(8976),EFG(45678),CDE(3456)	CDE	Looking or a Formula in J4		BCD	2345
5					CDE	3456
Sheet2 (2)

Cell Formulas
Range	Formula
J3:J4	J3	=LOOKUP(9^9,FIND("("&N$3:N$5&")",I3),M$3:M$5)

Hi Peter, Thank you. This is the formula that i was looking for and fits my requirement.

 

[Peter_SSs]

Hi Peter, Thank you. This is the formula that i was looking for and fits my requirement.

You're welcome. Glad it worked for you!

I am using 2013/2016 version

Please update your Account details with this information (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

 

[kishorenaidugurram]

You're welcome. Glad it worked for you!


Please update your Account details with this information (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

View attachment 38469

once again thanks Peter. I have updated the Account detais

 

[Peter_SSs]

I have updated the Account detais