[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I match  and populate a column between two worksheets

Posted on 2011-05-09
10
Medium Priority
?
236 Views
Last Modified: 2012-05-11
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
Comment
Question by:manelson05
  • 4
  • 3
  • 3
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720271
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
 
LVL 1

Expert Comment

by:TonyWong
ID: 35720338
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35720355
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Expert Comment

by:TonyWong
ID: 35720361
cheers rorya
0
 

Author Comment

by:manelson05
ID: 35721536
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35721595
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35721665
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
 
LVL 1

Expert Comment

by:TonyWong
ID: 35722007
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
 

Author Closing Comment

by:manelson05
ID: 35722135
Very helpfula s I continue to learn more about excel at a higher level.

Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question