[ACCEPTED]-How to import data from one sheet to another-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".
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:
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