[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VLookup - incorrect results - Excel 2007

Posted on 2011-04-21
3
Medium Priority
?
262 Views
Last Modified: 2012-05-11
I am using the following vlookup formula on a spreadsheet:

=VLOOKUP(B2,numbers!$A$2:$B$10,2)

My spreadsheet has two sheets:
Sheet 1 contains my vlookup formula in cell A2
My second sheet is named “numbers”

My problem is this:
The vlookup formula is not providing the correct results on all fields –
I show the results below – notice that “11120” and “11110” show a result on “sheet1” as “5” – the “11120” should show a result of “4” on sheet1
Sheet1 also shows wrong results for 11132 & 11133 (both shows “9” on Sheet1


Sheet1
col-a      col-b
1      10000
2      11000
3      11100
5      11120
5      11110
6      11126
7      11127
8      11128
9      11131
9      11132
9      11133


Numbers sheet:
col-a      col-b
10000      1
11000      2
11100      3
11120      4
11110      5
11126      6
11127      7
11128      8
11131      9
11132      10
11133      11


Any help you could provide would be appreciated.
0
Comment
Question by:mmj1
  • 2
3 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35442976

=VLOOKUP(B2,numbers!$A$2:$B$10,2,FALSE)

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35442992
The fourth parameter to the VLOOKUP function is range_lookup. From Excel Help:

Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
•If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
 Important   If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data in a range or table.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

•If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Kevin
0
 

Author Closing Comment

by:mmj1
ID: 35443190
Thanks very much -- that corrected my issue.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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