Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 97 Populating columns based on key fields

Status
Not open for further replies.

mooneye

Technical User
Jan 14, 2002
27
IE
I have 2 worksheets.
I need to map 2 columns of information contained in the 2nd worksheet into the 1st worksheet based on key fields contained in the 2nd worksheet.
The 1st worksheet contains multiple instances of the key fields in the 2nd worksheet.
I need to bring the additional information contained in the 2nd worksheet into the 1st worksheet for all instances of the key fields.

Any help would be appreciated
Mooneye
 
Hi Mooneye,

It appears that the VLOOKUP function might be your best solution.

First, with your reference to...

"The 1st worksheet contains multiple instances of the key fields in the 2nd worksheet."

Does this possibly mean the following...

1) That you have one key field in the 1st worksheet that contains multiple instances of record-identification codes ? - i.e. in multiple records, where the codes are all in the same field - for example in Column A.

2) Your data on your 2nd worksheet has one column with this same identification code, and 2 columns of information that you want to "map" to the 1st worksheet, placing this data opposite each of those records in the 1st worksheet having identification codes found in the 2nd worksheet ?

If the above basically is your situation, then the following should help you...

Steps:

1) Assign a range name to your table of data on the 2nd worksheet. I used the name "tbl". It's referenced in the formula below.

2) In your 1st worksheet, in the columns where you want the data from the 2nd worksheet "mapped" to, enter the following formulas - one for each of the two columns...

=IF(ISNA(VLOOKUP(A2,tbl,2,FALSE)),"",VLOOKUP(A2,tbl,2,FALSE))

=IF(ISNA(VLOOKUP(A2,tbl,3,FALSE)),"",VLOOKUP(A2,tbl,3,FALSE))

3) Once entered, copy these formulas down for all the records in your 1st worksheet.

These formulas are based on:
a) Your "record identification code" being in Column A, and
b) The 2 columns to be "mapped to" in your "tbl" range (on the 2nd sheet) are the 2nd and 3rd columns.

You'll obviously need to adjust the formulas to coincide with the actual columns of your application.

I hope this helps. :) Please advise as to how this fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top