I need help to solve for partial text index match please

[stephanie91]

Can someone please help me to solve this?
I need to find the customer ranking for each of the quotation reference under column K from the data in Column A & B. However, I am unable to do so as there are multiple quotation references in each of the Column A and the wildcard formula still returns as error. I am unsure if it is due to the commas between the references.

 

[Fluff]

Hi & welcome to MrExcel.
Can you post some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[stephanie91]

1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	QUOTE_REFERENCE	Customer Ranking									QUOTATION REFERENCE	CUSTOMER RANKING
2	CL000718479CO-10-002	1									CL000973089-A-009
3	CL000262470-13-001,CL001654607-A-001,CL001805363-13-001,CL003022569-A-001,CL005053163-A-001,CL0459945-13-001,CL1171706-13-002,CL1417499-13-001,CL1470626-13-001,CL2216357-13-001,FL0619-MBW-13-001,FL0818-MBW-A-001,FL1627-MBW-A-001,FL2284-MBW-A-001,FL2614-MBW-A-001,FL340-MEXWB-13-001,FL3562-MBW-13-002,FL3751-MBW-A-001,FL4709-MBW-13-001,FL491-MEXWB-13-001,FL6185-MBW-A-001,FL6221-MBW-A-001,FL6478-MBW-A-001,FL6497-MBW-A-001,FL707-MEXWB-13-001,FL7848-MBW-A-001,FL8934-MBW-A-001,FL903-MEXWB-13-002,FL9314-MXE-A-001,FL949-MEXWB-13-001,FL9605-MBW-A-001,FL9837-MBW-A-001,QHOF261815,QHOF261848	2									FL9837-MBW-A-001
4	CL000973089-A-009,QECK043496	5									QCHN074165
5	GA086R,GA086R-EUR,GA086R-LEAF	1
6	QCHN071595,QCHN071596,QCHN074165	5
Sheet1

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C								K	L
1	QUOTE_REFERENCE	Customer Ranking									QUOTATION REFERENCE	CUSTOMER RANKING
2	CL000718479CO-10-002	1									CL000973089-A-009	5
3	CL000262470-13-001,CL001654607-A-001,CL001805363-13-001,CL003022569-A-001,CL005053163-A-001,CL0459945-13-001,CL1171706-13-002,CL1417499-13-001,CL1470626-13-001,CL2216357-13-001,FL0619-MBW-13-001,FL0818-MBW-A-001,FL1627-MBW-A-001,FL2284-MBW-A-001,FL2614-MBW-A-001,FL340-MEXWB-13-001,FL3562-MBW-13-002,FL3751-MBW-A-001,FL4709-MBW-13-001,FL491-MEXWB-13-001,FL6185-MBW-A-001,FL6221-MBW-A-001,FL6478-MBW-A-001,FL6497-MBW-A-001,FL707-MEXWB-13-001,FL7848-MBW-A-001,FL8934-MBW-A-001,FL903-MEXWB-13-002,FL9314-MXE-A-001,FL949-MEXWB-13-001,FL9605-MBW-A-001,FL9837-MBW-A-001,QHOF261815,QHOF261848	2									FL9837-MBW-A-001	2
4	CL000973089-A-009,QECK043496	5									QCHN074165	5
5	GA086R,GA086R-EUR,GA086R-LEAF	1
6	QCHN071595,QCHN071596,QCHN074165	5
7
Master

Cell Formulas
Range	Formula
L2:L4	L2	=XLOOKUP("*"&K2&"*",$A$2:$A$6,$B$2:$B$6,"",2)

 

[stephanie91]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C								K	L
1	QUOTE_REFERENCE	Customer Ranking									QUOTATION REFERENCE	CUSTOMER RANKING
2	CL000718479CO-10-002	1									CL000973089-A-009	5
3	CL000262470-13-001,CL001654607-A-001,CL001805363-13-001,CL003022569-A-001,CL005053163-A-001,CL0459945-13-001,CL1171706-13-002,CL1417499-13-001,CL1470626-13-001,CL2216357-13-001,FL0619-MBW-13-001,FL0818-MBW-A-001,FL1627-MBW-A-001,FL2284-MBW-A-001,FL2614-MBW-A-001,FL340-MEXWB-13-001,FL3562-MBW-13-002,FL3751-MBW-A-001,FL4709-MBW-13-001,FL491-MEXWB-13-001,FL6185-MBW-A-001,FL6221-MBW-A-001,FL6478-MBW-A-001,FL6497-MBW-A-001,FL707-MEXWB-13-001,FL7848-MBW-A-001,FL8934-MBW-A-001,FL903-MEXWB-13-002,FL9314-MXE-A-001,FL949-MEXWB-13-001,FL9605-MBW-A-001,FL9837-MBW-A-001,QHOF261815,QHOF261848	2									FL9837-MBW-A-001	2
4	CL000973089-A-009,QECK043496	5									QCHN074165	5
5	GA086R,GA086R-EUR,GA086R-LEAF	1
6	QCHN071595,QCHN071596,QCHN074165	5
7
Master

Cell Formulas
Range	Formula
L2:L4	L2	=XLOOKUP("*"&K2&"*",$A$2:$A$6,$B$2:$B$6,"",2)

Hi, I tried the same formula while searching on google earlier, it shows #NAME?

 

[JEC]

And this one

=LOOKUP(1000,FIND(K2,$A$2:$A$6),$B$2:$B$6)

 

[Fluff]

That error suggest that either you are not using 365, or your version is seriously out of date.
If you are using 365 try updating it.

 

[stephanie91]

And this one

=LOOKUP(1000,FIND(K2,$A$2:$A$6),$B$2:$B$6)

Thank you! This works perfectly!