Solved

Excel Vlookup from other worksheet

Posted on 2014-09-11
10
167 Views
Last Modified: 2014-09-11
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,FALSCH))
0
Comment
Question by:Mandy_
  • 6
  • 4
10 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40316675
In your question, I assume SVERWEIS = VLOOKUP.

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
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40316697
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,SHEET1!$A$1:$H$1,0),FALSE)

When copied across, the reference to P1 will change to Q1 and R1.

Thanks
Rob H
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40316720
I'm getting #name? Pls see Sheet2 below
sheet2
and sheet1
sheet1
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40316735
Can you show screenshot including formula bar so we can see the formula in P2 or upload a workbook?

Thanks
Rob H
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40316800
The formula is the same as your formula you wrote above

=VLOOKUP(L2,SHEET1!$A$2:$H$200,MATCH(P$1,SHEET1!$A$1:$H$1,0),FALSE)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40317169
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,MATCH(P$1,SHEET1!$A$1:$H$1,0),FALSE)

See attached.

Thanks
Rob H
Mandy-vlookup.xlsx
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40317177
Just thought, maybe it is interpreting the headers CA4, CA8, CA10 etc as cell reference rather than text.
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40317586
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
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40317874
Works for me, amended version with formulas in P2 - R2.

Thanks
Rob
ee-example--2-.xlsx
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40318600
Now it's working for me, too. Thank you so much, rob
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now