Range contains multiple text

[bdtran]

Hello All,

I need your help to create a formula to get one of three results in column A with the following scenarios.

For example within the same range $B3:$L3, if there is letter "A" found, then the result should be "VALUE-A".
In case if there are letter "A" and "B" in the same range, then the result should be "VALUE-A-B".
And if the range has all three letters, A, B and C, then the result should be "VALUE-A-B-C" as A3.

Thank you in advance.
*BDT

A

B

C

D

E

F

G

H

I

J

K

L

1

VALUE-A

A

A

A

2

VALUE-A-B

A

B

A

B

3

VALUE-A-B-C

A

B

C

C

B

A

<tbody>
</tbody>

 

[Yongle]

Hi - this formula works with the data you supplied

In A2 copied down
=IF(COUNTIF(B2:L2,"A")=0,"",IF(COUNTIF(B2:L2,"B")=0,"A",IF(COUNTIF(B2:L2,"C")=0,"A-B","A-B-C")))

 

[Phuoc]

Try:

=IF(COUNTA(B2:L2)>0,"VALUE"&IF(COUNTIF(B2:L2,"A")>0,"-A","")&IF(COUNTIF(B2:L2,"B")>0,"-B","")&IF(COUNTIF(B2:L2,"C")>0,"-C",""),"")

 

[bdtran]

Yongle and Phuoc,

I was able to get the exactly result I need using your formulas.

Thanks much for your help.
*BDT

 

[Yongle]

thanks for the feedback