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

x
Solved

# VLookup - incorrect results - Excel 2007

Posted on 2011-04-21
Medium Priority
262 Views
I am using the following vlookup formula on a spreadsheet:

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

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

0
Question by:mmj1
• 2

LVL 81

Expert Comment

ID: 35442976

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

Kevin
0

LVL 81

Accepted Solution

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.

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

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

## Featured Post

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…
###### Suggested Courses
Course of the Month17 days, 21 hours left to enroll