VLOOKUP is very common, popular and widely used function in Excel and Google Sheets. But the majority of users complain that VLOOKUP is not working correctly or giving incorrect results. This is because of some limitations with the VLOOKUP function, and sometimes users also do not carefully follow its rules and syntax. Here, we are going to discuss some of the common errors and reasons why VLOOKUP does not work. Show Common VLOOKUP ErrorsIn this article, we are going to discuss VLOOKUP errors, like #NA, #VALUE, #REF, and VLOOKUP returning incorrect results. Now you are going to see the reasons for these errors and their solutions. VLOOKUP #NA errorWhen VLOOKUP formula cannot find a match, then this error displays, meaning “not available.” But it is always not correct that the lookup value is actually not available. There could be some reasons why VLOOKUP returns this error.
VLOOKUP #VALUE errorGenerally, if you enter wrong data type in the formula in Excel, then formula generates #Value error. But in the case of VLOOKUP function, there are following three reasons that should look into.
VLOOKUP #REF errorIf the index_number argument of VLOOKUP is greater than the number of columns in table_array, then the VLOOKUP function returns a #REF error. So you need to check and rectify the index_number supplied in function. VLOOKUP returning incorrect resultsIf you omit to supply match type in a range_lookup argument of VLOOKUP then by default it searches for approximate match values, if it does not find exact match value. And if table_array is not sorted in ascending order by the first column, then VLOOKUP returns incorrect results. Solution: You must always supply a relevant match type in a range_lookup argument of VLOOKUP as TRUE or FALSE. And in case of approximate match type (TRUE), you must always sort your table_array in ascending order by the first column of your table_array. Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here. Why is VLOOKUP not finding a value Google Sheets?Your Google Sheets VLOOKUP formula is set to return approximate match (TRUE), while the lookup column is not sorted. To fix that, either sort the lookup column A to Z or change TRUE to FALSE.
Why is my VLOOKUP missing values?If lookup value is not present in the first column of a table_array, then VLOOKUP generates #NA error. To fix this error, you must arrange your columns correctly and then select your table_array in VLOOKUP function.
Why is lookup not working Google Sheets?The LOOKUP function will only work properly if data in search_range or search_result_array is sorted. Use VLOOKUP, HLOOKUP, or other related functions if data is not sorted. search_key - The value to search for. For example, 42, "Cats", or I24.
What is not value in Google Sheets?NOT can be used in Google Sheets to exclude values from a range or to filter data. For example, if you want to exclude the value "Apple" from a range of cells, you can use the formula =NOT(A1:A5) to return the values in the range A1:A5 except for "Apple".
|