Solved

Help with Lookup table

Posted on 2013-12-17
9
322 Views
Last Modified: 2013-12-22
Hello,

I'm finding Lookup tables to be really helpful, but I still have some issues getting them to work.

Attached is a sample Excel 2007 file.  Sheet 1 is a list of documents in Column B.   Sheet 2 is another list of documents in Column A, along with additional columns with data that I want to bring to Sheet1.

I need to match the document name on Sheet 2 with the same document name on Sheet1. Then bring from Sheet 2 Columns A, B, C,D & E onto Sheet 1,  putting those columns on Sheet 1 starting at column C,D,E,F,G.

If there is no matching document in Sheet 2 then something needs to go into SHeet 1, I guess at column C saying "No match".

So the end result will be Sheet 1 including what is already in Column 1 and using the document name as the "key", and the matching data from Sheet 2,

Hope I am being clear and  not over explaining what is actually "simple". At least simple for you!

Thanks

Rowby
zap-docman-basics-for-experts-2.xlsx
0
Comment
Question by:Rowby Goren
  • 5
  • 4
9 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39725608
Here is how you would use vlookup in your situation. For the first ID the formula would be:

=IFERROR(VLOOKUP(B2,rokdownloads!$A$2:$B$15,2,FALSE),"No Match")

IFERROR is used to give you the "No Match" you wanted for records that don't appear on sheet 2. You would just copy that formula down. The next column, display name, would use:

=IFERROR(VLOOKUP(B2,rokdownloads!$A$2:$C$15,3,FALSE),"No Match")

We're just extending the table array by one column. That is what the "3" is before "False". It was "2" for ID and for Path it will be "4".
zap-docman-basics-for-experts-2.xlsx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39725613
I forgot to mention, the ID's in rows 9 and 10 are the same. This is because the name is the same. On sheet1, column A you have numbers listed that somehow are associated with the name. Those numbers do not appear in sheet2.

Flyster
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39725645
Thanks Flyster.  I will be testing this all out first thing in the morning, when my brain is better in gear.

I'm sure it will work fine!

Rowby
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 39726667
Hi Flyster,

Just looked at your demo.  Looks good. Let me apply it to my actual document.

Rowby
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39727135
Hmmm.  I added your code to my complete document and when I drag down, with the first formula I get no match.

There really isn't any personal data in my own file, so I am uploading it here.

I tried to match my file with the smaller version I uploaded to EE, but clearly I did something wrong.....

Thanks

Rowby
experts-why-not-working.xlsx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39728131
The sample given was base on the data you provided, which was 15 rows. Highlight column C, go to the Home tab and under Editing select replace. Replace $B$15 with $B$500. That will cover your current range. If you think your table will grow, you can increase the $B$500 value to fit your needs.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39728182
This is what it should look like
experts-why-not-working.xlsx
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 39734963
Thanks Flyster.

Your solution saved me a big headache!

Rowby
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39735096
Glad it worked out for you!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

810 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