0

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!

Link to Test Spreadsheet illustrating problem

0

1 Answer 1

1

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,)
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.