+ Reply to Thread
Results 1 to 8 of 8

VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    Hello,

    I have entered a VLOOKUP formula to populate cell B2 on worksheets 1 through .60 in the workbook. The formula returns the values from the MasterList sheet on all of the worksheets except worksheets named 2 through 12. I've entered the same formula on all of the sheets but cannot figure out why the lookup value will not return the information on these sheets. The formula I used is:

    =VLOOKUP($A$1,MasterList!$A$1:$b$60,2,0)

    Can some please take a look at the attached workbook and let me know what I am doing wrong? Thanks.
    Attached Files Attached Files

  2. #2
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    Excel 2016
    Posts
    231

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    Looks like you've got those cell formatted as "Text;" change them to "General."

  3. #3
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    Excel 2016
    Posts
    231

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    Also, looks like you're going to have to change the value of your references in A1 to text in order to get the lookups to work; I don't think VLOOKUP likes looking at formulas.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,729

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    One would have to review the actual steps you took to create these tabs. I noted that B1 in all sheets is formatted as text, so the question would be if they were formatted as text before or after trying to enter the formula. If they were pre-formatted as text, Excel will (usually) assume that anything entered is supposed to be a text literal and will ignore formulas. The solution is to make sure that a cell is formatted as "general" or something other than "text" so that excel will recognize the formula when you enter it.

    In this case, I am not sure what the text format is doing different from the general format, so I might suggest dropping the text format from all of these cells.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Contributor
    Join Date
    08-04-2004
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    186

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    It's odd. If I just press F2, highlight formula and then Ctrl-C hen Ctrl-V it comes up as a formula
    Try evaluate formula before and it says "This cell contains a constant"

  6. #6
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    Excel 2016
    Posts
    231

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    Quote Originally Posted by Mvaldesi View Post
    Looks like you've got those cell formatted as "Text;" change them to "General."
    Quote Originally Posted by Mvaldesi View Post
    Also, looks like you're going to have to change the value of your references in A1 to text in order to get the lookups to work; I don't think VLOOKUP likes looking at formulas.
    Lastly, you're going to have to convert the first few references on your MasterList tab to text by putting an apostrophe in front of the first digit.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-18-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    Wow...all of the advice worked like a charm. Thank you all so much. I have been racking my brain as I am fairly new and self taught with excel formulas but I am always trying to learn different things to make life a little easier. Love this site!!!

  8. #8
    Forum Contributor Mvaldesi's Avatar
    Join Date
    01-21-2011
    Location
    Plano, TX
    MS-Off Ver
    Excel 2016
    Posts
    231

    Re: VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value

    You're wel... oh, he's gone.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] vlook formula
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2016, 10:36 AM
  2. [SOLVED] Formula with vlook up
    By Rpatel1974 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-08-2015, 04:29 PM
  3. Replies: 2
    Last Post: 10-17-2012, 01:17 AM
  4. IF , VLOOK formula help
    By A-Ibraheem in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-19-2012, 03:55 PM
  5. [SOLVED] vlook up and if formula
    By Stuart in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 03:05 AM
  6. vlook up and if formula
    By Stuart in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 02:05 AM
  7. [SOLVED] vlook up and if formula
    By Stuart in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-06-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1