[ACCEPTED]-How to import data from one sheet to another-vlookup
VLookup
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".
External Reference
To reference 3 a cell of the same Workbook use the following 2 pattern:
<Sheetname>!<Cell>
Example:
Table1!A1
To reference a cell of a different Workbook 1 use this pattern:
[<Workbook_name>]<Sheetname>!<Cell>
Example:
[MyWorkbook]Table1!A1
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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.