Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Help with Lookup table

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
Rowby Goren
Asked:
Rowby Goren
  • 5
  • 4
1 Solution
 
FlysterCommented:
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
 
FlysterCommented:
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
 
Rowby GorenAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
Rowby GorenAuthor Commented:
Hi Flyster,

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

Rowby
0
 
Rowby GorenAuthor Commented:
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
 
FlysterCommented:
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
 
FlysterCommented:
This is what it should look like
experts-why-not-working.xlsx
0
 
Rowby GorenAuthor Commented:
Thanks Flyster.

Your solution saved me a big headache!

Rowby
0
 
FlysterCommented:
Glad it worked out for 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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