# Displaying Other Values Via COUNTIF in Excel

Basically I have a sheet like this:

``````         A                     B                  C             D
Name (From File 1)     Name (From File 2)    Phone Number    Countif

Walt                   Jesse                 5555555555      IF(COUNTIF(\$B\$3:\$B\$10,A3),A3,"No Match")
Jesse                  Mike                  5555555556      IF(COUNTIF(\$B\$3:\$B\$10,A4),A4,"No Match")
Skyler                 Saul                  5555555557      IF(COUNTIF(\$B\$3:\$B\$10,A5),A5,"No Match")
Hank                   Todd                  5555555558      IF(COUNTIF(\$B\$3:\$B\$10,A6),A6,"No Match")
Marie                  Gus                   5555555559      IF(COUNTIF(\$B\$3:\$B\$10,A7),A7,"No Match")
Saul                   Gomie                 5555555550      IF(COUNTIF(\$B\$3:\$B\$10,A8),A8,"No Match")
Huell                                                        IF(COUNTIF(\$B\$3:\$B\$10,A9),A9,"No Match")
Mike                                                         IF(COUNTIF(\$B\$3:\$B\$10,A10),A10,"No Match")
``````

Column D evaluates to:

``````   D
Countif

No Match
Jesse
No Match
No Match
No Match
Saul
No Match
Mike
``````

Column A is in a very specific order, and I need D column to return:

``````   D
Countif

No Match
Jesse_5555555555
No Match
No Match
No Match
Saul_5555555557
No Match
Mike_5555555556
``````

In other words, how do I get IF(COUNTIF()) to display a value from a cell next to a target? Would it be something like IF(COUNTIF(\$B\$3:\$B\$10,A3),CONCATENATE(A3,"_",???),"No Match")?

-

``````=IF(COUNTIF(\$B\$3:\$B\$10,A3),A3&"_"&VLOOKUP(A3,\$B\$3:\$C\$10,2,FALSE),"No Match")
You can simplify that a little: `=IFERROR(A3&"_"&VLOOKUP(A3,\$B\$3:\$C\$10,2,FALSE), "No Match")`. – Scott Aug 20 '13 at 23:35