Rowby Goren
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I'm sure it will work fine!
Rowby
ASKER
Hi Flyster,
Just looked at your demo. Looks good. Let me apply it to my actual document.
Rowby
Just looked at your demo. Looks good. Let me apply it to my actual document.
Rowby
ASKER
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
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
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.
This is what it should look like
experts-why-not-working.xlsx
experts-why-not-working.xlsx
ASKER
Thanks Flyster.
Your solution saved me a big headache!
Rowby
Your solution saved me a big headache!
Rowby
Glad it worked out for you!
Flyster