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

Solution 1

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:

<Sheetname>!<Cell>

Example:

Table1!A1

To reference a cell of a different Workbook use this pattern:

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

Example:

[MyWorkbook]Table1!A1

Solution 2

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.

Comments

[das_weezul]

A downvote? Why? An explanation would be nice, otherwise I don't know how to improve my answer.

[Hip Hip Array]

but you see the data i am entering is not in the same order(I have "ID" like 100168, 101584) and they are all over the place, they are not in the same order in both sheets. Thats why i need to look up where the "ID" is the same....... and i wasn't the one as well who put the down vote by the way. I appreciate the help you are giving :)

[das_weezul]

@Niall: That is exactly what VLOOKUP does. I could have changed the order in the second table. Sorry, I should have made that clear.

[DarkDust]

@das_weezul: The downvote was for the "I'll provide an answer later" version. Now that the answer is useful I've turned it into a +1.

[das_weezul]

@DarkDust: Thanks and sorry for that, I just wanted to point Niall in the right direction while working on the screenshot ;)

[Hip Hip Array]

sorry again, but how do you reference "sheet2" when getting the data?