Share via

copy data from one sheet to another based on column header

Anonymous
2016-07-08T22:32:37+00:00

On sheet "fruits", I want to evaluate the headers on row 2, (A2:K2) and find copy all of the data below the header and paste it into sheet "all" if the header in row 3 (B:J) is matched on the next available line in that column. 

Example:

Sheet Fruits

Sheet All

Kiwi is found so paste the values from the first page to this one. Grapes is found so copy the data from that one to this one. 

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Ashish Mathur 101K Reputation points Volunteer Moderator
    2016-07-08T23:08:02+00:00

    Hi,

    In cell B4 of sheet "All", enter this formula and copy down/right

    =IFERROR(INDEX(Fruits!$A$4:$C$6,ROW(1:1),MATCH(All!B$3,Fruits!$A$3:$C$3,0)),"")

    Hope this helps.

    4 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-07-11T13:50:29+00:00

    Thanx! 

    When I make my first million.... I'm buying you a Krystal with cheese Sir!

    (....or White Castle if you prefer :-) )

    0 comments
  2. Ashish Mathur 101K Reputation points Volunteer Moderator
    2016-07-11T15:06:47+00:00

    You are welcome. If my reply helped, please mark it as answer.

    0 comments