What's the best way to merge 2 tables keeping 1 column aligned?

Example: I have these 2 tables from which I want to make table 3:

color    name1
red      apple
orange   orange
yellow   banana
green    kiwi
purple   blueberry
name2      price
apple      $1
avocado    $2
banana     $.5
blueberry  $.1
A        B          C D          E
color    name1        name2      price
red      apple        apple      $1
orange   orange     
yellow   banana       banana     $1
green    kiwi       
purple   blueberry    blueberry  $.1
                      avocado    $2

I want name1 to align with name2 across. What's the best way to do this?

I've been doing it by adding a column between name1 and name2 (column C above) and =if(b2=d2,1,0) copy/paste down column C, and scanning by eye for 0. Not only is that tedious, but each time I insert a blank D&E cell, I need to recalculate C by hand. There's got to be a better/simpler way. I'm OK with VBA but having trouble with vlookup for some reason

Thank you in advance.
/non-excel expert


VLOOKUP should be able to help you here.

It has the following parameters,

  • The key you want to look up
  • The range of cells where you want to look for the value, The key will be searched for in the first column of that range.
  • the column offset of the value you want to get.

I like to keep the tables in separate sheets when doing things like this.

You need to have the key column (the things you want to match) in front of the values that you want to look up.

So if you're looking up names and want to get the value for the colour, your table should be

Sheet 1 your combined table, switch columns around as desired.

(A) name | ... | (D)colour | (E)price
blueberry| ... | blue      | =VLOOKUP(A8,Sheet2.$A$1:$B$500,2,0)

Sheet 2

(A)      | (B) price|
blueberry|      5.34|  

You have to be careful with VLOOKUP, usually you'll want to use fixed rows and numbers to define the area where you want to do the lookup ($ in front of rows and column references). This will make sure your lookup range stays fixed when you copy it for new rows.

There is one parameter at the end that determines wether or not to do a Range Lookup. (You don't want to do this in your case)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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