need formula or macro to compare a number to the second digit and return the first digit(s) if there is(are) a match(es)

[purelutz]

Hello there, i'm wondering if this is possible. i need a formula that will output the below data in column E-G based on given data in cells a-c and cells h-j.
For example in E1, i want to take 0 from A1 and compare it to the right-most digit from h1-j1 and if there is a match return the first digit, which is 1.
column E is for looking up column A, column F is to look up column B, and G for C. if there are multiple instances then also output those as well. if no match then simply output nothing or "-".
Thank you.

 

[DanteAmor]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2		1			13	14	10
2	0	1	2			1,2		11	7	21
3	0	1	2				3	4	5	32
4	0	1	2			1,3,5		1	31	51
5	0	1	2		2,3		2	20	22	30
sheet

Cell Formulas
Range	Formula
E1:G5	E1	=SUBSTITUTE(IF(""&A1=RIGHT($H1),","&LEFT($H1),"")&IF(""&A1=RIGHT($I1),","&LEFT($I1),"")&IF(""&A1=RIGHT($J1),","&LEFT($J1),""),",","",1)

 

[Peter_SSs]

Dante may well have answered your question already, but can you confirm what result should be in F1 of your sample if cell I1 just contained "1" instead of '14"?

 

[purelutz]

thanks good formula but 1 thing, to peter_: if the cell contained 1 instead of 14 the result should be 0. any way to modify the formula so that if there is a single digit return 0 because it's implicit (5 vs 05) but excel may not pick up on this? thank you.

 

[Peter_SSs]

if the cell contained 1 instead of 14 the result should be 0

Thanks, I thought that might be the case. However, I forgot to also ask you what version of Excel you are using.

If you have the TEXTJOIN function, then try this, copied across and down. (I have assume that 2 digits is the most that will appear in columns H:J)

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2		1			13	14	10
2	0	1	2			1, 2		11	7	21
3	0	1	2				3	4	5	32
4	0	1	2			0		6	5	1
5	0	1	2		2, 3		2	20	22	30
Compare

Cell Formulas
Range	Formula
E1:G5	E1	=TEXTJOIN(", ",1,IF(RIGHT($H1:$J1,1)+0=A1,LEFT(TEXT($H1:$J1,"00"),1),""))

 

[DanteAmor]

thanks good formula but 1 thing, to peter_: if the cell contained 1 instead of 14 the result should be 0. any way to modify the formula so that if there is a single digit return 0 because it's implicit (5 vs 05) but excel may not pick up on this? thank you.

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2		1			13	1	10
2	0	1	2			1,2		11	7	21
3	0	1	2				3	4	5	32
4	0	1	2			3,5		1	31	51
5	0	1	2		2,3		2	20	22	30
sheet

Cell Formulas
Range	Formula
E1:G5	E1	=SUBSTITUTE(IF(AND(LEN($H1)=2,""&A1=RIGHT($H1)),","&LEFT($H1),"")&IF(AND(LEN($I1)=2,""&A1=RIGHT($I1)),","&LEFT($I1),"")&IF(AND(LEN($J1)=2,""&A1=RIGHT($J1)),","&LEFT($J1),""),",","",1)

 

[Peter_SSs]

If you have the TEXTJOIN function ..

.. otherwise try

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	1	2		1			13	14	10
2	0	1	2			1, 2		11	7	21
3	0	1	2				3	4	5	32
4	0	1	2			0		6	5	1
5	0	1	2		2, 3		2	20	22	30
Compare (2)

Cell Formulas
Range	Formula
E1:G5	E1	=MID(IF(RIGHT($H1,1)+0=A1,", "&INT($H1/10),"")&IF(RIGHT($I1,1)+0=A1,", "&INT($I1/10),"")&IF(RIGHT($J1,1)+0=A1,", "&INT($J1/10),""),3,7)