• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

Excel: Wanted - Code to map fields and update data between two lists

I need to move table data from one excel list to another, based on a set of
field name mappings from one list to the other maintained on a third sheet.

I understand that the LOOKUP function will return the corresponding field name for the other sheet, but how do I move the data automatically?

I'd appreciate any pointers / sample code.

For e.g.,

Sheet 1 contains columns titled 'YTD' and 'Mech'
Sheet 2 contains columns titled 'Budget' and 'Spent'
Sheet 3 contains two columns holding the mapping list:
YTD -> Spent
Mech -> Budget

I want to automatically move data in the 'YTD' column on sheet 1 to the corresponding column in sheet 2 (the column 'Spent' in this case) based on the field name mappings maintained in sheet 3.

I hope the purpose is clear. Of course, my sheets have many more columns.

1 Solution
Hi Nasriram,

Let's say, your headers on sheet 1 are on line 1 in  the range A1:G1 (7 columns), you give it a name 'Headers' (Select headers row, go to menu Bar/Insert/Name/ Type "Headers" in the 'names in worksheet box', and click OK).

The Mapping list is on sheet 3, for example in range B1:C5 (that's 5 mappings). In this mapping list, for my below formula to work, the column labels of sheet2 must be in left column and the source column labels of sheet1 must be in right column.

Then in cell B2 of sheet 2 (the header names must be on line 1), enter this formula, and copy it down to all other cells.


That should do it.

If you enter a line, where in sheet1 no data is entered yet, the formula will return a #REF result. That's normal.
If you want to transform this error message into a blank, transform the formula to:


Good Luck

nasriramAuthor Commented:
I'd have taken forever to work out that formula... thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now