How to Correct VLOOKUP error (#N/A,#REF!,#VALUE!,#NAME?)

This article explains different errors which VLOOKUP result can show, reasons for the same and how to handle VLOOKUP error. In addition to this, the precaution to be taken while using VLOOKUP to get correct result is also explained.

#N/A error in VLOOKUP

#N/A error occurs when the result is not found by VLOOKUP formula.

This error can happen following cases:

When range_lookup is TRUE or omitted in formula (Approximate Match)

#N/A error in VLOOKUP
  • If the Table array is not sorted in ascending order.
  • If the lookup_value is smaller than the value in left most column of table_array.

When range_lookup is FALSE in formula (Exact Match)

  • If the exact match to lookup_value is not available in the left most column of table_array or lookup_value is not given.
  • The intended lookup column is not the leftmost column of the table array.
  • A typo or misprint in the lookup_value
  • Numbers are formatted as text in lookup_value or table_array
  • Excess leading or trailing spaces in lookup_value or table_array

Solutions for #N/A error in VLOOKUP

  • Ensure that the data table is sorted in ascending in case if the range lookup is TRUE (Approximate Match)
  • Ensure that lookup column is the leftmost column of the table array. If it is not the left most column, you can read this article 3 ways to lookup left in excel
  • Ensure that the lookup_value is defined correctly.
  • Check the formatting of in lookup_value and table_array, if both formats are different, make both as same format.
  • Check for extra space or unseen characters both in lookup_value and table_array. You can Remove Extra Spaces from Lookup Values using  VLOOKUP with TRIM
  • You can use IFERROR or ISERROR to hide this error.

#REF! Error in VLOOKUP

#REF! Error may occur in VLOOKUP in following cases:

  • The references given in the formula does not exists. This may happen when you delete rows or columns in sheet, or if you copy paste the formula without using absolute reference or fixing the references.
  • The column_index mentioned in the formula is greater than the number of columns in the table_array.
vlookup error

Solutions for VLOOKUP error #REF!

  • Check for #REF! values in the Formula and correct the references.
  • Use absolute reference instead of relative reference in formula. This will avoid #REF! error in case of copy paste of formula.
  • Check the col_index_num argument it should be less than or equal to number of column in the table.

#VALUE! Error in VLOOKUP

#VALUE! Error may occur in VLOOKUP in following cases:

  • The supplied col_index_num argument is < 1 or is not defined in formula.
  • In case if the lookup_value exceeds 255 characters.

Solutions for #VALUE! error in VLOOKUP

  • Correct the col_index_num in the formula.
  • Ensure that the lookup value is less than 255 character.

#NAME? Error in VLOOKUP

#NAME? Error may occur in VLOOKUP in following cases:

  • In case if you misspelled the function’s name in the formula.
  • The supplied col_index_num is not recognised as number in formula.
  • The supplied [range_lookup] argument is not recognised as one of the logical values TRUE or FALSE

Solutions for #NAME? error in VLOOKUP

  • Check and correct the function name in formula as VLOOKUP
  • Check that the comulm index number is defined properly as number.
  • Correct the lookup range as TRUE or FALSE / 1 or 0

These are the vlookup error s and solutions to correct the errors. To avoid showing this error, we can use ISERROR or IFERROR function. IF you find any other error which may occur in VLOOKUP please comment below.

2 thoughts on “How to Correct VLOOKUP error (#N/A,#REF!,#VALUE!,#NAME?)”

  1. It would really helps a lot while using vlookup. Nice sharing.
    Please keep sharing such a great tips and tricks on MS Excel.

    Reply
    • Thank you very much for your feedback..
      I will update the blog on regular basis..

      Reply

Leave a Comment