Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VLOOKUP with other sheet

Posted on 2012-12-21
9
Medium Priority
?
378 Views
Last Modified: 2012-12-31
Hi. When I use the following VLOOKUP on the same sheet as the range "staff" it works fine.
It doesn't, however, work on a different sheet

=VLOOKUP(A5,staff,6,0)
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 668 total points
ID: 38712395
it works for me just fine; what error do you get?
and with "other sheet" you mean really just another sheet in the same workbook?
can you upload the file (or a sample where you reproduce the issue)?
0
 
LVL 54

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 668 total points
ID: 38712396
if on sheet1

=VLOOKUP(A5,Sheet1!staff,6,0)
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 664 total points
ID: 38712413
Agree with AngelIII, works OK for me - you shouldn't need to refer to the sheet for a named range, is the lookup value the same, where is A5?

regards, barry
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38712456
Is the A5 on the same sheet?
0
 

Author Comment

by:Murray Brown
ID: 38712466
Here is the file. I am trying to do a VLOOKUP on the sheet "Template" that references the Sheet Data1
Scheduler.xls
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38712489
This formula works for me

=VLOOKUP(A5,staff,4,0)

.....but the names need to match - you have "Anthony" on one sheet (with an "h") and "Antony" (without "h") on the other

regards, barry
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 38712495
Also I think the names need to be in order to get correct results
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38712516
>Also I think the names need to be in order to get correct results

No, that's not correct - VLOOKUP with a 4th argument of FALSE or zero will give you an exact match only - the lookup range doesn't need to be sorted (that only applies when 4th argument of VLOOKUP is TRUE or 1)

The formula used should work OK on the data given, as long as the lookup value finds a match

regards, barry
0
 

Author Closing Comment

by:Murray Brown
ID: 38732083
Thanks for the help
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

597 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