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

How do I match and populate a column between two worksheets

I am wanting to be able to take information from the TRACKING tab and place it in the REPORT tab under the Serial Number column I created. I need to match by DEVICENUM, where ever DEVICENUM matches between REPORT and TRACKING (source data) I need the Assett and Serial Number to print in REPORT. If Assett is missing I need to print out null.
ee-match-example.xls
0
manelson05
Asked:
manelson05
  • 4
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
In F2:
=IF(ISNA(MATCH($E2,Tracking!$D:$D,0)),"",INDEX(Tracking!E:E,MATCH($E2,Tracking!$D:$D,0)))
copy across and down.

See attached file.
ee-match-example.xls
0
 
TonyWongCommented:
Hi there,

Please see the attached book.

I used a VLOOKUP function, which will mean that the Tracker sheet will need to have the asset number column as the first column and have it sorted A-Z - VLOOKUP needs the reference to be in the first column and sorted in A-Z order.

Cheers,
Tony
0
 
Rory ArchibaldCommented:
FYI, VLOOKUP does not require the table to be sorted if you are doing an exact match (as we are here) - you just need to specify FALSE as the fourth argument. INDEX and MATCH is easier here as it does not require the source columns to be in any order.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
TonyWongCommented:
cheers rorya
0
 
manelson05Author Commented:
It worked in my test fiel I tried on my production file and I cant get it to populate? shall Ipost my Production file?
0
 
Rory ArchibaldCommented:
Yes please - should only need a couple of rows of data to match on (as long as the matching data is in the lookup table!)
I suspect as you are looking up numbers, one may be stored as text and the other as numeric data.
0
 
manelson05Author Commented:
0
 
Rory ArchibaldCommented:
Your formula is wrong - in C2 it should be:
=IF(ISNA(MATCH($B2,SOURCE!$D:$D,0)),"",INDEX(SOURCE!E:E,MATCH($B2,SOURCE!$D:$D,0)))

rather than:
=IF(ISNA(MATCH($B2,SOURCE!$D:$D,0)),"",INDEX(SOURCE!E:E,MATCH($E2,SOURCE!$D:$D,0)))
0
 
TonyWongCommented:
Just to let you know, I had a look at the file earlier (before posting my (bad) solution) and noticed that the devicenum in Tracking tab was formatted as 'Special' and the devicenum in Report was formatted as text.

Cheers,
Tony
0
 
manelson05Author Commented:
Very helpfula s I continue to learn more about excel at a higher level.

Thank you
0
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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