VLookup - incorrect results - Excel 2007

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.
mmj1Asked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:

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

Kevin
0
 
mmj1Author Commented:
Thanks very much -- that corrected my issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.