TEXTJOIN with exact match

[nburaq]

Hi,
I would like find the values based on exact match via textjoin function but it gives results even with partial matches. What is the correct formula for that or can you fix mine? Thanks for your help.

Location	Value
ABC	101​
ABC-00A	105​
ABC-00B	106​
ABC-00B-01	110​

I use formula similar to :
=TEXTJOIN(",",TRUE,IF($A$2:$A$5=E2,$B$2:$B$5,""))

If I try only to find value of "ABC" then it brings all the values starting with ABC. Hence instead of showing only value 101 it shows 101,105,106,110

 

[Fluff]

What is in E2?

 

[nburaq]

What is in E2?

Search keyword like "ABC"

 

[Fluff]

In that case your formula works for me

Fluff.xlsm
	A	B	C	D	E
1	Location	Value
2	ABC	101			ABC
3	ABC-00A	105			101
4	ABC-00B	106
5	ABC-00B-01
Main

Cell Formulas
Range	Formula
E3	E3	=TEXTJOIN(",",TRUE,IF($A$2:$A$5=E2,$B$2:$B$5,""))

 

[nburaq]

In that case your formula works for me

Fluff.xlsm
	A	B	C	D	E
1	Location	Value
2	ABC	101			ABC
3	ABC-00A	105			101
4	ABC-00B	106
5	ABC-00B-01
Main

Cell Formulas
Range	Formula
E3	E3	=TEXTJOIN(",",TRUE,IF($A$2:$A$5=E2,$B$2:$B$5,""))

Ok, then something might be wrong with the table I pull from the database. Thanks for the confirmation I thought my formula is not working even I was trying to build something TEXTJOIN(ISNUMBER(SEARCH...

 

[Fluff]

You're welcome.

 

[nburaq]

You're welcome.

How can I convert formula to get output as "Not Covered" in a way if it there is no result available. Thanks again

 

[Peter_SSs]

Is this what you mean?

23 02 21.xlsm
	A	B	C	D	E
1	Location	Value
2	ABCD	101			ABC
3	ABC-00A	105			Not Covered
4	ABC-00B	106
5	ABC-00B-01	111
TJ

Cell Formulas
Range	Formula
E3	E3	=LET(tj,TEXTJOIN(",",TRUE,IF($A$2:$A$5=E2,$B$2:$B$5,"")),IF(tj="","Not Covered",tj))

 

[Peter_SSs]

.. or perhaps this instead?

23 02 21.xlsm
	A	B	C	D	E
1	Location	Value
2	ABCD	101			ABC
3	ABC-00A	105			Not Covered
4	ABC-00B	106
5	ABC-00B-01	111
TJ

Cell Formulas
Range	Formula
E3	E3	=TEXTJOIN(",",1,FILTER(B2:B5,A2:A5=E2,"Not Covered"))

 

[nburaq]

.. or perhaps this instead?

23 02 21.xlsm
	A	B	C	D	E
1	Location	Value
2	ABCD	101			ABC
3	ABC-00A	105			Not Covered
4	ABC-00B	106
5	ABC-00B-01	111
TJ

Cell Formulas
Range	Formula
E3	E3	=TEXTJOIN(",",1,FILTER(B2:B5,A2:A5=E2,"Not Covered"))

I tried that but then it is giving "CALC! error