Xlookup across large amounts of data in a cell

[eechristaylor]

I have a cell with data in it that I know is in the match criteria. The data is in a cell with a large amount of data with it separated by a ";". How can I get it to recognize the data in a large string where it is seperated by a ";"?

For example:

24​					1​	10;24;15;16;17;18;28
101​					2​	11;16;50;55;101;111;124

In B1 I have =XLOOKUP(A1,G1:G2,F1:F2,"",0) looking to return a "1"

Any help would be appreciated!

Thanks

 

[jasonb75]

I don't believe that you can do wildcard matching with xlookup.

=INDEX($F$1:$F$2,MATCH("*;"&A1&";*",";"&$G$1:$G$2&";",0))

edit:- you can do wildcard matching with xlookup, there is an optional parameter for it that I had missed previously.

=XLOOKUP("*;"&A1&";*",";"&G1:G2&";",F1:F2,,2)

 

[eechristaylor]

I don't believe that you can do wildcard matching with xlookup.

=INDEX($F$1:$F$2,MATCH("*;"&A1&";*",";"&$G$1:$G$2&";",0))

edit:- you can do wildcard matching with xlookup, there is an optional parameter for it that I had missed previously.

=XLOOKUP("*;"&A1&";*",";"&G1:G2&";",F1:F2,,2)

Thanks so much but I only got them to work partially, I am working with a big data set and for the xlookup it doesnt pick up the second number in the A row (say A3 was 18 it just shows this as a blank instead of returning the '1").

Then on the INDEX/MATCH combo I get it to work on some but have #N/A where I confirm there is a match, trying to figure out this issue.

 

[Alex Blakenburg]

I am not really clear on the requirement, a XL2BB screenshot with more rows would help.
Is each lookup only looking on the current row and trying to find if the value in the left column eg column A is in the delimited string in column G on the same row and returning a position number into column F on the same row ?

 

[jasonb75]

Both formulas work fine for me. The ranges are set to the example, for a bigger data set you will need to make sure that the ranges in the formula cover the entire data set.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	24	1	1			1	10;24;15;16;17;18;28
2	101	2	2			2	11;16;50;55;101;111;124
3	18	1	1
4	10	1	1
5	124	2	2
Sheet2

Cell Formulas
Range	Formula
B1:B5	B1	=INDEX($F$1:$F$2,MATCH("*;"&A1&";*",";"&$G$1:$G$2&";",0))
C1:C5	C1	=XLOOKUP("*;"&A1&";*",";"&$G$1:$G$2&";",$F$1:$F$2,,2)