Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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! – Jarrett G. 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .