Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I have two excel work books, one has two columns. First column contains a part number and the second contain the description for the part number to the right of it. In the second workbook i have a list of parts. that i need to match descriptions to, using the data in the first workbook. Is this possible to do?
Is the description in the second column the same as the "list of parts" in the other workbook?
If you have 3 columns that don't have anything in common, it would really tough to match things up.
Perhaps if you provided some examples of your data it would help us help you.
0 
Exp
Column 1 Column 2 Column 3
1 5 Red
2 4 Blue
3 3 Green
4 2 Orange
5 1 BlackI need to match Column 3 to column 1. Column two and 3 are in the same workbook. So i would like a formula or macro to find the number one in the second column then put the coresponding color in the column next to Column one.
0
Column 1 Column 2 Column 3
1__________5__________red
2__________4_________black
3__________3_________orange
4__________2_________green
5__________1_________whiteThat is a better look
0
Have you tried VLOOKUP? Works for me...
=VLOOKUP(A1,'C:\Documents and Settings\user_name\Desktop\[Book2.xls]Sheet1'!$B$1:$C$5,2,0)You need to make sure you have the full path name in the formula. This is most easily done by saving both files first so that when you select the lookup_array as you build the formula, it will automagically insert the path after the workbook is closed.
0
I used VLOOKUP but it is not always linking the correct value. It will give me a value but it is not matched correctly to the part number. If i put in False for exact match. It does not give me any value just states N/A, and only works on the first cell and nothing past it.
0
I used your example data, putting 1 - 5 in A1:A5 of Book1.xls and 5 - 1 and the colors in B1:C5 of Book2.xls.
The formula I posted is placed in B1 of Book1 and dragged down to B5. It returns the the correct color every time. I can change the colors in Book2, save and close it, and when I open Book1 it updates the colors to match the changes.
Since the only thing I have to work with is your example data and you didn't post the formula that you tried, I can't offer any suggestions as to why your VLOOKUP isn't working.
0
I have put all the data i need in three columns. Similar to what i showed you at the first time. =VLOOKUP(A1,B1:C20000,2) that is what i am using. What will happen, is the first row will give me the correct answer but after that it will give me the wrong return. There are a few cells in the A column that do not have a value in the table_array. Not sure if that would affect the return values.
0
Once again without seeing your actual workbook, I am limited in what suggestions I can offer.
First, if you are using the exact formula that you have posted and are dragging it down, let say in Column D, then your lookup_array is changing in each cell.
You need to use Absolute Referencing to keep your lookup_array constant. Highlight B1:C20000 in your first formula and hit F4. You should see this:
=VLOOKUP(A1,$B$1:$C$20000,2)
Now when you drag it down, the $B$1:$C$20000 will not change.
Second, since you have omitted the range_lookup argument, it defaults to TRUE meaning that it will try to find an approximate match if it can't find an exact match. Read the VLOOKUP Help file in Excel to see how it handles what it calls an "approximate match".
If you want it to find only exact matches, use this:
=VLOOKUP(A1,$B$1:$C$20000,2,0)
Finally, if you don't want to see the #N/A error should one legally occur, you can use something like this:
=IF(ISNA(VLOOKUP(A1,$B$1:$C$20000,2,0)),
"No Match Found",VLOOKUP(A1,$B$1:$C$20000,2,0))I hope something here helps.
0
I do have a sample worksheet i can give you so you can look at it. I am sure i have not been too clear. So if you have a email or something, I can attach the sample. That is if you have time. Thanks for all your help.
Scott
savestheday85203@yahoo.com
0
I've sent you an email. Reply with the spreadsheet and I'll see what I can do.
Please do not share the email address with anyone.
0
hi every 1 i have a question, i tried vlookup, but didn't work;
please some 1 tell me i would like to give a cell a name(abriviation), so whenever i type the abbriviation automaticaly give me the full name in very same cell, e.g;
cel 1 cel2
r ranger
b bahterin another cell different sheet whenever i type r automaticaly give me ranger in same cell.
thanks in advance.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
