Solved

Vlookup

Posted on 2013-11-25
3
320 Views
Last Modified: 2013-11-25
I can NEVER get VLOOKUP to work, but my colleague has no issue.  Please see attached file and advise what the heck I am doing wrong.

Thanks.
VLOOKUP.xlsx
0
Comment
Question by:iarkowski
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39676004
VLOOKUP wants to find the lookup value in the first column of the lookup table. That would be column B on worksheet Test, suggesting a formula like:
=VLOOKUP(B2,Test!B$2:E$62,4,FALSE)
0
 

Author Closing Comment

by:iarkowski
ID: 39676024
Superb!!!!!
0
 
LVL 81

Expert Comment

by:byundt
ID: 39676028
Breaking the VLOOKUP formula apart:
=VLOOKUP(B2,Test!B$2:E$62,4,FALSE)
Look for the value in B2 in the leftmost column of the lookup table.
The lookup table is in Test!B$2:E$62.
The 4 means you want a value from the fourth column of the lookup table (column E).
The FALSE means that the leftmost column in the lookup table hasn't been sorted. Furthermore, you need an exact match for the value in B2.

If VLOOKUP can't find B2 in the leftmost column of the lookup table, it returns the #N/A! error value. In your original formula, you were looking for B2 in Test column A. It's not there, so VLOOKUP dutifully returned #N/A! as the result.

Another common reason for VLOOKUP failing to return a value would be if B2 is text that looks like a number and Test column B contains numbers. VLOOKUP won't find "5" if Test column B contains 5. The converse is also true.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
My experience with Windows 10 over a one year period and suggestions for smooth operation
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
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…

778 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