V or Xlookup multiple values in single cell

[gmooney]

Hi,

I have a column of data that contains multiple numbers in a single cell that are seperated by using the ALT-Enter option like below.

I want to look up each of these values and compare to another file that has only 1 value per cell in a column. How can I accomplish this and also how would I handle the fact that I could get either no results or up to 3 results back?

301 305 310

815 825 845

 

[JamesCanale]

This one is fun:

MrExcelPlayground20.xlsx
	A	B
1	301 305 310	is a silly
2	815 825 845	fat goose.
3	435 436 438
4	115 120 125	Jamie
5
6
7
8	115	Jamie
9	301	is
10	305	a
11	310	silly
12	815	fat
13	845	goose.
Sheet21

Cell Formulas
Range	Formula
B1:B4	B1	=LET(a,VALUE(TEXTSPLIT(A1,,CHAR(10))),b,XLOOKUP(a,$A$8:$A$13,$B$8:$B$13,"",0),TEXTJOIN(CHAR(10),FALSE,b))

 

[gmooney]

This one is fun:

MrExcelPlayground20.xlsx
	A	B
1	301 305 310	is a silly
2	815 825 845	fat goose.
3	435 436 438
4	115 120 125	Jamie
5
6
7
8	115	Jamie
9	301	is
10	305	a
11	310	silly
12	815	fat
13	845	goose.
Sheet21

Cell Formulas
Range	Formula
B1:B4	B1	=LET(a,VALUE(TEXTSPLIT(A1,,CHAR(10))),b,XLOOKUP(a,$A$8:$A$13,$B$8:$B$13,"",0),TEXTJOIN(CHAR(10),FALSE,b))

Hi there....Thank you...That did what I needed however the number of lines per cell to look up will not always be 3. I have some cells that have up to 24 lines of either 3 or 4 character text.

 

[Peter_SSs]

That did what I needed however the number of lines per cell to look up will not always be 3. I have some cells that have up to 24 lines of either 3 or 4 character text.

In what way does the suggested formula not work in those circumstances?
I have also included a slight variation in case you didn't want the blank lines in the results.

gmooney.xlsm
	A	B	C
1	3015 305 310	is a silly	is a silly
2	6845	goose.	goose.
3	435 436 438 8888
4	115 120 3015 222 305 310 815 6845	Jamie is a silly fat goose.	Jamie is a silly fat goose.
5
6
7
8	115	Jamie
9	3015	is
10	305	a
11	310	silly
12	815	fat
13	6845	goose.
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=LET(a,VALUE(TEXTSPLIT(A1,,CHAR(10))),b,XLOOKUP(a,$A$8:$A$13,$B$8:$B$13,"",0),TEXTJOIN(CHAR(10),FALSE,b))
C1:C4	C1	=LET(a,VALUE(TEXTSPLIT(A1,,CHAR(10))),b,XLOOKUP(a,$A$8:$A$13,$B$8:$B$13,"",0),TEXTJOIN(CHAR(10),TRUE,b))