Mandy_
asked on
Excel Vlookup from other worksheet
Hi,
i need help with a vlookup formula.
example
sheet1 data from A2:H200
A F G H
2 abc 1 2 3
3 dfg 4 5 6
4 hij 7 8 9
5 klm
6 nop
7 qrs
8 tuv
vxy
sheet 2 with data in L2 as A2 in sheet1
now with vlookup should fill in sheet2 Cell P2, Q2, R2 with F2, G2, H2 from sheet1 if match with L2 (range sheet1 A2;A200)
for example if Sheet2 Cell L2 = ABC Sheet2 P2 should be =1 Q2 =2 and R2=3
&SVERWEIS(L2,SHEET1!$A$2:$ H$200,2,FA LSCH))
i need help with a vlookup formula.
example
sheet1 data from A2:H200
A F G H
2 abc 1 2 3
3 dfg 4 5 6
4 hij 7 8 9
5 klm
6 nop
7 qrs
8 tuv
vxy
sheet 2 with data in L2 as A2 in sheet1
now with vlookup should fill in sheet2 Cell P2, Q2, R2 with F2, G2, H2 from sheet1 if match with L2 (range sheet1 A2;A200)
for example if Sheet2 Cell L2 = ABC Sheet2 P2 should be =1 Q2 =2 and R2=3
&SVERWEIS(L2,SHEET1!$A$2:$
If you have the same headers on source and destination tables, you can use a MATCH formula to find the correct column; assuming headers in row 1:
=VLOOKUP(L2,SHEET1!$A$2:$H $200,MATCH (P$1,SHEET 1!$A$1:$H$ 1,0),FALSE )
When copied across, the reference to P1 will change to Q1 and R1.
Thanks
Rob H
=VLOOKUP(L2,SHEET1!$A$2:$H
When copied across, the reference to P1 will change to Q1 and R1.
Thanks
Rob H
Can you show screenshot including formula bar so we can see the formula in P2 or upload a workbook?
Thanks
Rob H
Thanks
Rob H
ASKER
The formula is the same as your formula you wrote above
=VLOOKUP(L2,SHEET1!$A$2:$H $200,MATCH (P$1,SHEET 1!$A$1:$H$ 1,0),FALSE )
=VLOOKUP(L2,SHEET1!$A$2:$H
The #Name? error would suggest that it is looking for a Range Name within the formula, bizarre!! Maybe the headers don't quite match.
I have thrown together an example and it works for me. One minor tweak I have made is locking the reference to L2:
=VLOOKUP($L2,SHEET1!$A$2:$H$200,MATC H(P$1,SHEE T1!$A$1:$H $1,0),FALS E)
See attached.
Thanks
Rob H
Mandy-vlookup.xlsx
I have thrown together an example and it works for me. One minor tweak I have made is locking the reference to L2:
=VLOOKUP($L2,SHEET1!$A$2:$H$200,MATC
See attached.
Thanks
Rob H
Mandy-vlookup.xlsx
Just thought, maybe it is interpreting the headers CA4, CA8, CA10 etc as cell reference rather than text.
ASKER
Dear Rob,
Your example is working. But in my file created with excel 2013 or Excel MAC 2011 i'm still getting
"The formula has an error" and this part of the formula is mark:
=VLOOKUP($A2,SHEET1!$A$2:$H$200,MATCH( P$1,SHEET1 !$A$1:$H$1 ,0),FALSE)
here my example
ee-example.xlsx
Your example is working. But in my file created with excel 2013 or Excel MAC 2011 i'm still getting
"The formula has an error" and this part of the formula is mark:
=VLOOKUP($A2,SHEET1!$A$2:$H$200,MATCH(
here my example
ee-example.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now it's working for me, too. Thank you so much, rob
You have the syntax of the formula correct. Increase the offset number (2 in your posted example) to refer to the column that you want to return.
In your example, changing to 6 will pull data from column F as it is the 6th column of your data table. 7 for column G, 8 for column H.
For future reference, the offset number is relative to the starting column of the table, not the physical column of the sheet. In your example they are the same but if your table started in column C and you wanted the 6th column it would pull data from column H.
Thanks
Rob H