Excel - Search for a string then add some values (Entered to many arguments)











up vote
0
down vote

favorite












I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.



I was getting #NA as a return value so I added IFNA() to each lookup function which results in "To many arguments" error.



here is what the current state looks like



=(SUM(IFNA(VLOOKUP(A4,Nation2017,2),0),IFNA(VLOOKUP(A4,Nation20018,2),0),IFNA(VLOOKUP(A4,Nation2016,2),0,),IFNA(VLOOKUP(A4,Nation2015,2),0))


Any help is much appresiated



apologises I should have mentioned the Nation20018 misspelling isn't the issue I misspelt it and never corrected it



EDIT: I'll leave the question open as none of the suggested solutions did the job I did it manually at the end which was tedious, to say the least.










share|improve this question




















  • 1




    The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
    – n8te
    Dec 7 at 3:24












  • The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
    – Scott
    Dec 7 at 3:40















up vote
0
down vote

favorite












I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.



I was getting #NA as a return value so I added IFNA() to each lookup function which results in "To many arguments" error.



here is what the current state looks like



=(SUM(IFNA(VLOOKUP(A4,Nation2017,2),0),IFNA(VLOOKUP(A4,Nation20018,2),0),IFNA(VLOOKUP(A4,Nation2016,2),0,),IFNA(VLOOKUP(A4,Nation2015,2),0))


Any help is much appresiated



apologises I should have mentioned the Nation20018 misspelling isn't the issue I misspelt it and never corrected it



EDIT: I'll leave the question open as none of the suggested solutions did the job I did it manually at the end which was tedious, to say the least.










share|improve this question




















  • 1




    The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
    – n8te
    Dec 7 at 3:24












  • The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
    – Scott
    Dec 7 at 3:40













up vote
0
down vote

favorite









up vote
0
down vote

favorite











I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.



I was getting #NA as a return value so I added IFNA() to each lookup function which results in "To many arguments" error.



here is what the current state looks like



=(SUM(IFNA(VLOOKUP(A4,Nation2017,2),0),IFNA(VLOOKUP(A4,Nation20018,2),0),IFNA(VLOOKUP(A4,Nation2016,2),0,),IFNA(VLOOKUP(A4,Nation2015,2),0))


Any help is much appresiated



apologises I should have mentioned the Nation20018 misspelling isn't the issue I misspelt it and never corrected it



EDIT: I'll leave the question open as none of the suggested solutions did the job I did it manually at the end which was tedious, to say the least.










share|improve this question















I have 5 sheets with country data, I want to lookup the country name on 4 separate sheets within 4 separate ranges then take a value from the row and add all those values.



I was getting #NA as a return value so I added IFNA() to each lookup function which results in "To many arguments" error.



here is what the current state looks like



=(SUM(IFNA(VLOOKUP(A4,Nation2017,2),0),IFNA(VLOOKUP(A4,Nation20018,2),0),IFNA(VLOOKUP(A4,Nation2016,2),0,),IFNA(VLOOKUP(A4,Nation2015,2),0))


Any help is much appresiated



apologises I should have mentioned the Nation20018 misspelling isn't the issue I misspelt it and never corrected it



EDIT: I'll leave the question open as none of the suggested solutions did the job I did it manually at the end which was tedious, to say the least.







microsoft-excel worksheet-function spreadsheet






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 7 at 12:47

























asked Dec 7 at 3:20









JavaScrub

11




11








  • 1




    The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
    – n8te
    Dec 7 at 3:24












  • The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
    – Scott
    Dec 7 at 3:40














  • 1




    The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
    – n8te
    Dec 7 at 3:24












  • The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
    – Scott
    Dec 7 at 3:40








1




1




The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
– n8te
Dec 7 at 3:24






The first and easiest thing to check when you get an error like that is to count the total opening and closing brackets. You'll notice in your case that they do not match.
– n8te
Dec 7 at 3:24














The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
– Scott
Dec 7 at 3:40




The second argument to your second VLOOKUP is Nation20018. Perhaps you meant Nation2018?
– Scott
Dec 7 at 3:40










1 Answer
1






active

oldest

votes

















up vote
0
down vote













I'm assuming that Nation2015 & others are Named Range.



Instead of modify the existing Formula I would like to suggest a simple Formula using CONCATENATE to get Country Name from 4 separate sheets within 4 separate ranges and to add them to make one value.



=CONCATENATE(VLOOKUP(A4,Nation2017,2,FALSE)&" "&(VLOOKUP(A4,Nation2018,2,FALSE)&" "&VLOOKUP(A4,Nation2016,2,FALSE)&" "&VLOOKUP(A4,Nation2015,2,FALSE))) 



  • Adjust cell references in the Formula as needed.

  • Check Nation20018 I also guess, it should be Nation2018 instead.






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',
    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%2f1381535%2fexcel-search-for-a-string-then-add-some-values-entered-to-many-arguments%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








    up vote
    0
    down vote













    I'm assuming that Nation2015 & others are Named Range.



    Instead of modify the existing Formula I would like to suggest a simple Formula using CONCATENATE to get Country Name from 4 separate sheets within 4 separate ranges and to add them to make one value.



    =CONCATENATE(VLOOKUP(A4,Nation2017,2,FALSE)&" "&(VLOOKUP(A4,Nation2018,2,FALSE)&" "&VLOOKUP(A4,Nation2016,2,FALSE)&" "&VLOOKUP(A4,Nation2015,2,FALSE))) 



    • Adjust cell references in the Formula as needed.

    • Check Nation20018 I also guess, it should be Nation2018 instead.






    share|improve this answer

























      up vote
      0
      down vote













      I'm assuming that Nation2015 & others are Named Range.



      Instead of modify the existing Formula I would like to suggest a simple Formula using CONCATENATE to get Country Name from 4 separate sheets within 4 separate ranges and to add them to make one value.



      =CONCATENATE(VLOOKUP(A4,Nation2017,2,FALSE)&" "&(VLOOKUP(A4,Nation2018,2,FALSE)&" "&VLOOKUP(A4,Nation2016,2,FALSE)&" "&VLOOKUP(A4,Nation2015,2,FALSE))) 



      • Adjust cell references in the Formula as needed.

      • Check Nation20018 I also guess, it should be Nation2018 instead.






      share|improve this answer























        up vote
        0
        down vote










        up vote
        0
        down vote









        I'm assuming that Nation2015 & others are Named Range.



        Instead of modify the existing Formula I would like to suggest a simple Formula using CONCATENATE to get Country Name from 4 separate sheets within 4 separate ranges and to add them to make one value.



        =CONCATENATE(VLOOKUP(A4,Nation2017,2,FALSE)&" "&(VLOOKUP(A4,Nation2018,2,FALSE)&" "&VLOOKUP(A4,Nation2016,2,FALSE)&" "&VLOOKUP(A4,Nation2015,2,FALSE))) 



        • Adjust cell references in the Formula as needed.

        • Check Nation20018 I also guess, it should be Nation2018 instead.






        share|improve this answer












        I'm assuming that Nation2015 & others are Named Range.



        Instead of modify the existing Formula I would like to suggest a simple Formula using CONCATENATE to get Country Name from 4 separate sheets within 4 separate ranges and to add them to make one value.



        =CONCATENATE(VLOOKUP(A4,Nation2017,2,FALSE)&" "&(VLOOKUP(A4,Nation2018,2,FALSE)&" "&VLOOKUP(A4,Nation2016,2,FALSE)&" "&VLOOKUP(A4,Nation2015,2,FALSE))) 



        • Adjust cell references in the Formula as needed.

        • Check Nation20018 I also guess, it should be Nation2018 instead.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 7 at 5:48









        Rajesh S

        3,5671522




        3,5671522






























            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.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • 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%2f1381535%2fexcel-search-for-a-string-then-add-some-values-entered-to-many-arguments%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

            Probability when a professor distributes a quiz and homework assignment to a class of n students.

            Aardman Animations

            Are they similar matrix