I need to match two columns (B and C) and move down the values (C and D) inserting an empty line where there's no match.

what I have

into this

what I need

Is there a formula that can do this?

  • I've tried IFERROR and MATCH functions, and a whole lot of internet searching resulting in solutions that get me partway there, but not inserting the blank lines. I'm newer to functions in Excel so any pointing in the right direction would help. – user651504 Oct 12 '16 at 17:37
up vote 0 down vote accepted

You should have the tables structured so that the leftmost column is the key, i.e. the unique identifier. In this case, that would be their names. After that, you can simply use VLOOKUP to look up a key in a second table. You can wrap that VLOOKUP in an IFERROR to hide errors by making them equal empty strings (i.e. "")

In your example, column C is redundant, so here's how I'd structure it:

enter image description here

For copy-and-paste purposes, the formula in C1 is =IFERROR(VLOOKUP($A1,$A$7:$B$9,2,0),"") -- then just drag down

  • For the record, I always make my hardcoded cells blue and my formulas black. It's just the standard in the world of Finance and it really helps keep track of what's doing what in your worksheet as it gets more complex. – Andre Terra Oct 12 '16 at 15:03
  • 1
    That is awesome!! Thanks for teaching me. – user651504 Oct 12 '16 at 21:01
  • @user651504 My pleasure! I remember struggling with all of this not too long ago. For the record, if you found that this answers your question appropriately, please upvote it and mark as accepted when you have a chance. And good luck! – Andre Terra Oct 12 '16 at 21:04

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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