[ACCEPTED]-How to import data from one sheet to another-vlookup

Accepted answer
Score: 12


You can do it with a simple VLOOKUP formula. I've 8 put the data in the same sheet, but you 7 can also reference a different worksheet. For 6 the price column just change the last value 5 from 2 to 3, as you are referencing the 4 third column of the matrix "A2:C4". VLOOKUP example

External Reference

To reference 3 a cell of the same Workbook use the following 2 pattern:




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



Score: 7

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

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

Here is a link with the 19 appropriate formulas.

The Sheet 2 formula 18 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 17 information on the Index/Match formula

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

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

Good luck! Hope this helps.

More Related questions