How to import data from one sheet to another

Question

I have two different worksheets in excel with the same headings in all cells of row 1 (a1 = id, b1 = name, c1 = price). My question is, is there a way to import data (for example, name) from 1 sheet to another, where "id" is the same on both sheets.

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 “Name” in sheet 1 “Name” in sheet 2, where “ID” in sheet 1 = “ID” in sheet 2?

Do not cope and insert, of course, Thanks

Answer 1

VPR

You can do this with a simple VLOOKUP formula. I put the data in one sheet, but you can also link to another worksheet. For the price column, just change the last value from 2 to 3, since you are referring to the third column of the matrix "A2: C4".

External reference

To reference a cell in the same book, use the following template:

 <Sheetname>!<Cell> 

Example:

 Table1!A1 

To reference a cell in another book, use this template:

 [<Workbook_name>]<Sheetname>!<Cell> 

Example:

 [MyWorkbook]Table1!A1 

Answer 2

I saw this thread, looking for something else, and I know that it is very old, but I wanted to add my 2 cents.

NEVER USE VLOOKUP. This is one of the worst formulas in excel. Use index match instead. It even works without sorting the data, if you do not have -1 or 1 at the end of the correspondence formula (explained below)

Here is the link with the corresponding formulas.

The formula for sheet 2 will be: = IF (A2 = "," ", INDEX (Sheet1! B: B, MATCH ($ A2, Sheet1! $ A: $ A, 0)))

• IF (A2 = "," "means that if A2 is empty, return an empty value
• INDEX (Sheet1! B: B says INDEX B: B, where B: B is the data you want to return. IE is the name column.
• Match (A2, says to match A2, which is the identifier you want to return for the name.
• Sheet1! A: A, says you want to map A2 to the ID column on the previous sheet.
• 0)) indicates that you want the exact value. 0 means it returns an exact match with A2, -1 means the smallest return value greater than or equal to A2, 1 means the return of the largest value that is less than or equal to A2. Keep in mind that -1 and 1 need to be sorted.

Additional Information on the Index / Match Formula

Other interesting facts: $ means the absolute value in the formula. Therefore, if you specify $ B $ 1 when filling in the formula up or down, it will keep the same value. If you have exceeded $ B1, then B still remains unchanged by the formula, but if you fill, the value 1 increases with the count of the lines. Similarly, if you used B $ 1, padding to the right will increase B, but keep the link of line 1.

I also included the use of indirect in the second section. What indirectly allows you to use the text of another cell in the 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 same formula, but uses the column heading to change the search criteria.

Good luck Hope this helps.