Hi Ken,
that result is came out like 1_5_3_4. i want the result is come out like
test "All',"susie_lucy"...

"Ken Johnson" wrote:

On Oct 14, 12:34 pm, freefree
wrote:
To Anyone:
how to use If function to do below scenario:-

susie lucy eric henry result
1 5 3 4 All
2 7 susie_henry
9 8 lucy_eric
7 5 eric_henry
5 5 susie_eric
5 8 susie_lucy
2 henry
3 eric
9 lucy
4 susie
4 6 lucy_henry

apprieciated your help. thks

One way...

=IF(COUNTBLANK(A2:D2)=4,"",LEFT(IF(ISNUMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""),LEN(IF(ISN UMBER(A2),$A
$1&"_","")&IF(ISNUMBER(B2),$B$1&"_","")&IF(ISNUMBE R(C2),$C
$1&"_","")&IF(ISNUMBER(D2),$D$1&"_",""))-1))

Ken Johnson