Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup? to match column in two sheets
Hi, I'm trying to replace a value across two sheets based on a match in another column between the two sheets. What I have is sheet1 with two columns, the first is a vendor and the second an ID number. A B Hone 420 Cope 347 Fara 14 In sheet2, I have the same values in column A, but the value in column B may or may not be different. Also the value in col A may be repetitive. A B Hone 420 Hone 421 Hone 421 Cope 345 Cope 345 Fara 14 What I want to do is find all the values in sheet2 Col A that match sheet1 Col A and then drop in the value in Col B from sheet1 into sheet2. The masterlist contains 40000 lines and sheet 2 contains only 16000. Sheet2 may not have all values in col A that are in Col A of sheet1. I'm thinking VLOOKUP, but I'm unsure how to write the formula. Any help would be great. Thanks. -- researcy ------------------------------------------------------------------------ researcy's Profile: http://www.excelforum.com/member.php...o&userid=31948 View this thread: http://www.excelforum.com/showthread...hreadid=516849 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup? to match column in two sheets
Researchy,
Here is a vlookup formula: =VLOOKUP(Sheet2!A2:A2,Sheet1!$A$2:$B$40000,2,FALSE ) When you paste this formula into Sheet2 cell B2, it takes the value in Sheet2 cell A2 and finds the row with a match on Sheet1, column A (between rows 2 and 40000) and returns the column B value on that row to the cell with the formula on sheet2. Paste this into cell B2 of Sheet2 then copy it down. If your data doesn't start in row 2 of Sheet2, then change the Sheet2!A2:A2 to Sheet2!An:An where n is the first row of data. When you copy the formula down, "n" will automatically increment to match the row number for that cell. It also assumes that the data on Sheet1 are in rows 2 to 40000. So you need to modify the Sheet1!$A$2:$B$40000 to reflect the actual beginning and ending rows on Sheet1. The "$" will prevent the row numbers from changing when you copy the formula down. After you have "updated" all of the values, you may want to decide whether to leave the formulas in place or you can convert all of the formulas to values by doing a copy, then Paste Special Values. HTH, Brian researcy wrote: Hi, I'm trying to replace a value across two sheets based on a match in another column between the two sheets. What I have is sheet1 with two columns, the first is a vendor and the second an ID number. A B Hone 420 Cope 347 Fara 14 In sheet2, I have the same values in column A, but the value in column B may or may not be different. Also the value in col A may be repetitive. A B Hone 420 Hone 421 Hone 421 Cope 345 Cope 345 Fara 14 What I want to do is find all the values in sheet2 Col A that match sheet1 Col A and then drop in the value in Col B from sheet1 into sheet2. The masterlist contains 40000 lines and sheet 2 contains only 16000. Sheet2 may not have all values in col A that are in Col A of sheet1. I'm thinking VLOOKUP, but I'm unsure how to write the formula. Any help would be great. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup? to match column in two sheets
Thanks Brian. It works like a charm and now that I understand how the formula is laid out, I know I can manipulate it in case the sheet name changes or more rows are added. Thanks again. -- researcy ------------------------------------------------------------------------ researcy's Profile: http://www.excelforum.com/member.php...o&userid=31948 View this thread: http://www.excelforum.com/showthread...hreadid=516849 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Which to use - if, vlookup, match, index, offset, vba? | Excel Discussion (Misc queries) | |||
VLOOKUP or MATCH | Excel Worksheet Functions |