VLOOKUP returning wrong row

Question

I have a fairly simple lookup table:

The formula I have in B6 is:

=VLOOKUP(A6,C1:D4,2)

So I would expect it to return 1, not 4?

Answer 1

You are omitting the sorted flag, which then defaults to TRUE thus making it ignore the exact match.

Quoting Google Help:

It’s recommended to set is_sorted to FALSE. If set to FALSE, an exact match is returned. If there are multiple matching values, the content of the cell corresponding to the first value found is returned, and #N/A is returned if no such value is found.

Check this image out:

Hope that helps, cheers!

Answer 2

you miss the 4th vlookup parameter. use this:

=VLOOKUP(A6, C1:D4, 2, 0)

or:

=VLOOKUP(A6, C1:D4, 2, ) 

or:

=VLOOKUP(A6, C1:D4, 2, FALSE)

to get exact match

Answer 3

VLOOKUP(search_key, range, index, [is_sorted])

Where

is_sorted - [optional]

Indicates whether the column to be searched (the first column of the 
   specified range) is sorted, in which case the closest match for 
   search_key will be returned.

so

=VLOOKUP(A6,C1:D4,2) ==> will give you 4, because is_sorted=1, sorted of the              
                         column that to be search. If you change A6=Monthly,
                         it will give you 2

to give you as is, no sorted, so you assign the is_sorted to 0

=VLOOKUP(A6,C1:D4,2,0)