Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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")?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

This will work, but it assumes there are not duplicates in column B

=IF(COUNTIF($B$3:$B$10,A3),A3&"_"&VLOOKUP(A3,$B$3:$C$10,2,FALSE),"No Match")
share|improve this answer
    
Perfect! Thanks a ton! –  Jart Aug 20 '13 at 23:24
2  
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
    
I like it. This function won't be recognized in Excel 2003 if that matters –  datatoo Aug 20 '13 at 23:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.