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.

  • 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

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.