Sign up ×
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:


No Match
No Match
No Match
No Match
No Match

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


No Match
No Match
No Match
No Match
No Match

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
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


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.