vlookup in a cell that contains mid formula












2















I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.



My vlookup formula is



=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)


Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?










share|improve this question




















  • 1





    It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

    – bdr9
    Jul 21 '14 at 1:28













  • Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

    – ben
    Jul 21 '14 at 12:01











  • If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

    – datatoo
    Jul 23 '14 at 20:43
















2















I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.



My vlookup formula is



=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)


Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?










share|improve this question




















  • 1





    It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

    – bdr9
    Jul 21 '14 at 1:28













  • Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

    – ben
    Jul 21 '14 at 12:01











  • If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

    – datatoo
    Jul 23 '14 at 20:43














2












2








2








I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.



My vlookup formula is



=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)


Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?










share|improve this question
















I am trying to create a vlookup in a cell referencing a specific cell so that I can pull the data from my lookup table. Well this cell that I am referencing happens to have a mid formula in it, and when I put my vlookup in the blank cell referencing this cell (as i want the actual value) I get #N/A.



My vlookup formula is



=vlookup(F57,'LOCATION LOOKUP TABLE'!B:C,2,0)


Can someone tell me if I need to remove the formula from the other cell so that my formula will work or do someone have another way that I can make this work without changing anything?







microsoft-excel microsoft-excel-2007






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jul 21 '14 at 2:59









Sathyajith Bhat

52.8k29156252




52.8k29156252










asked Jul 21 '14 at 1:05









user348257user348257

1112




1112








  • 1





    It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

    – bdr9
    Jul 21 '14 at 1:28













  • Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

    – ben
    Jul 21 '14 at 12:01











  • If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

    – datatoo
    Jul 23 '14 at 20:43














  • 1





    It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

    – bdr9
    Jul 21 '14 at 1:28













  • Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

    – ben
    Jul 21 '14 at 12:01











  • If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

    – datatoo
    Jul 23 '14 at 20:43








1




1





It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

– bdr9
Jul 21 '14 at 1:28







It shouldn't make a difference whether the cell targeted by the VLOOKUP contains a formula or a fixed value. Can you describe what is in columns B and C on the LOCATION LOOKUP TABLE sheet and what is in cell F57?

– bdr9
Jul 21 '14 at 1:28















Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

– ben
Jul 21 '14 at 12:01





Since you set the fourth argument to FALSE (0), VLOOKUP returns #N/A if an exact match is not found. As bdr9 already said, it doesn't matter if the specified table array itself contains formulas.

– ben
Jul 21 '14 at 12:01













If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

– datatoo
Jul 23 '14 at 20:43





If the value is in the table you are looking at, it may be the mid formula is return a text format which will not match a numeric value, even if they appear the same. Thus bdr9 is right to ask what the content in the location lookup table is

– datatoo
Jul 23 '14 at 20:43










1 Answer
1






active

oldest

votes


















0














To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.



So, if you expect a number, you can just multiply the result by 1:



for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .



If you do the same inside your vlookup -->



Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)



you'll assure that both your values are numbers.



It should work.






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "3"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f785632%2fvlookup-in-a-cell-that-contains-mid-formula%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.



    So, if you expect a number, you can just multiply the result by 1:



    for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .



    If you do the same inside your vlookup -->



    Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)



    you'll assure that both your values are numbers.



    It should work.






    share|improve this answer




























      0














      To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.



      So, if you expect a number, you can just multiply the result by 1:



      for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .



      If you do the same inside your vlookup -->



      Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)



      you'll assure that both your values are numbers.



      It should work.






      share|improve this answer


























        0












        0








        0







        To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.



        So, if you expect a number, you can just multiply the result by 1:



        for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .



        If you do the same inside your vlookup -->



        Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)



        you'll assure that both your values are numbers.



        It should work.






        share|improve this answer













        To add to these comments, your question lacks information but I'll guess that the result of your mid() function is probably in text type.



        So, if you expect a number, you can just multiply the result by 1:



        for example, mid(A2, 3,2) becomes mid(A2,3,2)*1 .



        If you do the same inside your vlookup -->



        Vlookup(F57*1,'LOCATION LOOKUP TABLE'!B:C,2,0)



        you'll assure that both your values are numbers.



        It should work.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered May 13 '15 at 16:02









        P. O.P. O.

        245112




        245112






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Super User!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f785632%2fvlookup-in-a-cell-that-contains-mid-formula%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            flock() on closed filehandle LOCK_FILE at /usr/bin/apt-mirror

            Mangá

            Eduardo VII do Reino Unido