# How to fix VLOOKUP when it returns the incorrect value from the bottom of the list?

In Google Sheets, I am having trouble with a VLOOKUP function.

• `range` argument `A2:B` contains text strings.
• `search_key` is in column C, example, `C2`
• `index` argument is (column) `2`, which is equivalent to `B2:B`

## Current Formula

• I thought my syntax was correct, but my VLOOKUP formula returns the wrong value.
• In column A are the textual search keys and in column B are the values that I wish to return. Values are sorted.
• Note that `#value!` in IFNA is intentional. I want to use the results in a TOCOL function and it was not treating `""` as a blank ("ignore blanks") so I replaced `""` with `#value!` and used "ignore error" instead.
``````=ifna(vlookup(\$C2,\$A\$2:\$B,2),#value!)
``````

## Sample Data

• `B2:B` strings are actually 50 characters in length (not 10); they've been shortened below to save space
A B C D E
1 search_key list of values user_input current formula desired result
2 mm 1101000000 mm `0100000000` 1101000000
3 1100000000 #VALUE!
4 mn 1100001000 mn `0100000000` 1100001000
5 1100000010 #VALUE!
6 ʙʛ `0100000000` #VALUE!

## Basic Formula

``````=IFNA(VLOOKUP(C2, A2:B, 2, 0))      // Data Unsorted

=IFNA(VLOOKUP(C2, SORT(A2:B), 2))   // Data Unsorted

=IFNA(VLOOKUP(C2, A2:B, 2))         // Sorted data (asc.)
``````

## Single Array Formula in `D2`

``````=ARRAYFORMULA(
IFNA(VLOOKUP(C2:C, A2:B, 2 ,0)))        // Data Unsorted

=ARRAYFORMULA(
IFNA(VLOOKUP(C2:C, SORT(A2:B), 2)))  // Data Unsorted

=ARRAYFORMULA(
IFNA(VLOOKUP(C2:C, A2:B, 2)))           // Sorted data (asc.)
``````

## Explantation

1. The VLOOKUP function's `is_sorted` argument refers to the `range` argument's first column, not the `index` argument's column.
2. That column needs to be sorted ascending either manually, or using the SORT function. Otherwise, the [optional] `is_sorted` argument should be set to `FALSE` / `0`  because it defaults to `TRUE` if omitted:
``````=VLOOKUP(search_key, range, index, [is_sorted])
``````
3. TOCOL does not see an empty string `""` as a blank value. By contrast the LEN function will treat `""` and blanks the same way: `LEN("")=0`.
4. To return a truly blank value, you can simply omit the `value_if_na_error` argument from the IFNA function:
``````Syntax:  IFNA(value, [value_if_na_error])

Replace:  IFNA(value,"")
By:  IFNA(value)
or By:  IFNA(value,)
``````