How to import data from one sheet to another

Question

I have two different work sheets in excel with the same headings in in all the row 1 cells a1   id  b1   name  c1   price   My question is  is there a way to import data like the name  from 1 worksheet to the other where the  id  is the same in both worksheets   eg  sheet 1                             sheet2 ID      Name       Price            ID        Name        Price xyz     Bag        20               abc                     15 abc     jacket     15               xyz                     20   So is there a way to add the  Name  in sheet 1 the  Name  in sheet 2 where the  ID  in sheet 1    ID  in sheet 2   Without coping and pasting of course Thanks

User · Answer

Saw this thread while looking for something else and I know it is super old, but I wanted to add my 2 cents.

NEVER USE VLOOKUP. It's one of the worst performing formulas in excel. Use index match instead. It even works without sorting data, unless you have a -1 or 1 in the end of the match formula (explained more below)

Here is a link with the appropriate formulas.

The Sheet 2 formula would be this: =IF(A2="","",INDEX(Sheet1!B:B,MATCH($A2,Sheet1!$A:$A,0)))

IF(A2="","", means if A2 is blank, return a blank value
INDEX(Sheet1!B:B, is saying INDEX B:B where B:B is the data you want to return. IE the name column.
Match(A2, is saying to Match A2 which is the ID you want to return the Name for.
Sheet1!A:A, is saying you want to match A2 to the ID column in the previous sheet
,0)) is specifying you want an exact value. 0 means return an exact match to A2, -1 means return smallest value greater than or equal to A2, 1 means return the largest value that is less than or equal to A2. Keep in mind -1 and 1 have to be sorted.

More information on the Index/Match formula

Other fun facts: $ means absolute in a formula. So if you specify $B$1 when filling a formula down or over keeps that same value. If you over $B1, the B remains the same across the formula, but if you fill down, the 1 increases with the row count. Likewise, if you used B$1, filling to the right will increment the B, but keep the reference of row 1.

I also included the use of indirect in the second section. What indirect does is allow you to use the text of another cell in a formula. Since I created a named range sheet1!A:A = ID, sheet1!B:B = Name, and sheet1!C:C=Price, I can use the column name to have the exact same formula, but it uses the column heading to change the search criteria.

Good luck! Hope this helps.

User · Answer

VLookup  You can do it with a simple VLOOKUP formula  I ve put the data in the same sheet  but you can also reference a different worksheet  For the price column just change the last value from 2 to 3  as you are referencing the third column of the matrix  A2 C4     External Reference  To reference a cell of the same Workbook use the following pattern    lt Sheetname gt   lt Cell gt    Example   Table1 A1   To reference a cell of a different Workbook use this pattern     lt Workbook name gt   lt Sheetname gt   lt Cell gt    Example    MyWorkbook Table1 A1

[excel] How to import data from one sheet to another

Examples related to excel

Examples related to worksheet-function

Examples related to vlookup