Excel Cell Reference Problem

Posted on 2011-10-24
Last Modified: 2012-05-12
I have 2 data sets.  I am pulling a small set of data from a larger set using VLOOKUP.  For some reason, it is not finding the data, even though it is there.  I know there is an issue with formatting.  For example, If I am trying to Lookup "John Smith", it will not return a solution, but when I manually go to the sheet, retype "John Smith" in the same place, the same way, it finds it and returns a solution to my Lookup page.  What am I missing?  I tried changing the formatting to "text" on both sheets (in the Format Cells Menu), but that hasn't worked.  It only seems to work when I re-type the exact same thing in the box.
Question by:Frankkadaf
    LVL 19

    Expert Comment

    It would be easier to diagnose if you provide an example but the most common problem with a vlookup is not using the final parameter for example: =vlookup(a1,$B1$C55,3,0) - the final ",0" tells vlookup that it should be looking for an exact match and the data in column B is not sorted.

    If you provide a sample file we can examine what's going on

    LVL 10

    Accepted Solution

    check if there are spaces before/after the data you are looking in.

    Author Closing Comment

    There was 1 space after the first name in the large data set!  A small but important difference!

    LVL 10

    Expert Comment

    Glad to help!

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now