merge two sheets and keep the redundant data

Question

I have 2 excel sheets.

First one is like this:

folk_id name first_name town_name
001     blog joe        alamo
002     folk james      brisbane
003     paco yatan      barcelona    

Second one is like this:

folk_id music_instrument  color
001     banjo             blue
001     guitar            green 
001     piano             yellow
002     harmonica         grey
002     harpschichord     red
003     violin            orange
003     bass              fuschia

Results would be like this

  folk_id name first_name town_name music_instrument  color
  001     blog joe        alamo     banjo             blue
  001     blog joe        alamo     guitar            green
  001     blog joe        alamo     piano             yellow
  002     folk james      brisbane  harmonica         grey
  003     paco yatan      barcelona harpschichord     red
  003     paco yatan      barcelona violin            orange
  003     paco yatan      barcelona bass              fuschia

I've tried with vlookup or index but I'm bit confused on how to do it.

I'm looking for a formula and not VBA.

Any help would be great.

Thanks!

Answer 1

Take a look:

The data is in two sheets. On Sheet1, enter the formula into cell D2. Then copy the formula across to F2 and then down as many rows as you need:

=iferror(vlookup($A2,Sheet2!$D$1:$F$100,column(A1),false),"")

copy across and down.