WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

VLOOKUP Not Working!

9/28/2014

0 Comments

 

Picture
Every once in a while either myself or a colleague experiences problems with their VLOOKUP formula in MS Excel. The VLOOKUP would not indicate the similar values between the list index and the source and would only return #N/A outputs while similar values do indeed exist between the two lists.

I have been able to diagnose the most common reasons for these anomalies based on my own analysis and also research on the web. Here are three solutions for fixing problems associated with VLOOKUP. I have listed them in order of importance:

1. Replace Extra Spaces with "No Space"
​

In the screenshot below, the "Last Name" column is the lookup value and the "Active" column is the index. As is evident, the formula is returning #N/A despite the fact that the lookup values are present in the table array.
In order to spot the problem, you need to highlight your lookup values and click your cursor past the very end of the entry. Here's the extra space at the end of the input.
Picture
Picture
To fix the problem, Click "CTRL+F" and replace spaces with no space in the field that the lookup being done (i.e. Column B). I.e. in the "Find what" section push the space bar button once, and leave the "Replace with" section intact.
Here is the output :). As you could see the VLOOKUP is now operating correctly as it has identified the Lookup values in the table array.
Picture
 
2. Reference Lock Your Array
​

In the screenshot below, the VLOOKUP has not returned any values for the last two lookup values: Daniel, and Lemme.
Picture
The screen shot below illustrates the reason for why the VLOOKUP did not pickup the return values for Daniel and Lemme. The reason is simply due to the fact that the table array is not the same across all return value rows.
Picture
In order to fix this issue all we have to do is to reference lock the table array. This is done by placing dollar ($) signs in the table array range. After placing the dollar signs, as illustrated in the screenshot below, the return values for the VLOOKUP have been corrected accordingly.
Picture

3. Multiply Your Lookup Values by 1
​

In the screenshot below I have used the values in column C as the lookup values. Notice that these values are numeric. There is a common problem that occurs often when using numeric values as the lookup value in a VLOOKUP. This problem arises when numbers are formatted as text.
Picture
Notice the apostrophe before the intended number in the screenshot below. This is due to the way these numbers have been formatted and/or the way they are imported into the spreadsheet.
Picture
In order to resolve this issue, I have multiplied all values in the ID column (i.e. column C) by 1. the result of the multiplication is inserted in column D in the screenshot below. After performing the multiplication we must also change the table array reference in the VLOOKUP to have it reference the new ID column.
Picture
Reference: http://www.randomwok.com/excel/top-3-reasons-why-my-vlookup-isnt-working/ 

0 Comments
<<Previous

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me