Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-24-2017, 08:56 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default VLOOKUP for country / country code

I tried many formulas and either I get #VALUE or #N/A,
someone please help.


=VLOOKUP(A1,Sheet2!A15:B37,2,0)

I have in Sheet 2

cell A15 | B15

Australia | 61
Canada | 1


China | 86
Reply With Quote
  #2  
Old 09-24-2017, 09:27 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,309
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Depending on whether A1 contains the:
• number
=INDEX(Sheet2!A15:A37,MATCH(A1,Sheet2!B15:B37,0))
• name
=INDEX(Sheet2!B15:B37,MATCH(A1,Sheet2!A15:A37,0))
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-24-2017, 11:51 PM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Thanks for your quick reply.

I got it working with
=VLOOKUP(A1,Sheet2!A15:B37,2)

The formula works fine, except when the lookup cell is
empty, I get #N/A. I believe it can show an empty cell
with ISBLANK...what should be the code?
I tried =IF(ISBLANK(=VLOOKUP(A1,Sheet2!A15:B37,2)) but
doesn't work.
Reply With Quote
  #4  
Old 09-24-2017, 11:56 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Try =IFERROR(VLOOKUP(A1,Sheet2!A15:B37,2),"")
Be aware that INDEX/MATCH combination is much more flexible ( and faster on large ranges) than VLOOKUP
Reply With Quote
  #5  
Old 09-25-2017, 01:53 AM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

=IFERROR.....didn't work.

I tried the Index/Match combination
=INDEX(Sheet2!B15:B37(MATCH(A1,Sheet2!A15:A37,0))

it may work...but I am getting an
error message saying "Your formula is missing a parenthesis--) or (.



Quote:
Originally Posted by Pecoflyer View Post
Try =IFERROR(VLOOKUP(A1,Sheet2!A15:B37,2),"")
Be aware that INDEX/MATCH combination is much more flexible ( and faster on large ranges) than VLOOKUP
Reply With Quote
  #6  
Old 09-25-2017, 02:07 AM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Problem solved !

here's the formula :

=IFERROR(INDEX(Sheet2!$B$15:B44,1*(MATCH(Sheet2!A1,Labels!$A$15:$A$37,0))),"")

1* was missing from your formula.

Thank you so much
Reply With Quote
  #7  
Old 09-25-2017, 03:35 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 132
Debaser is on a distinguished road
Default

You don't need 1* but you did need the comma in front of MATCH.
Reply With Quote
  #8  
Old 09-25-2017, 04:05 AM
mikehk mikehk is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 15
mikehk is on a distinguished road
Default

Awesome!
Removed 1*

Thanks again.
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Limit min and max number to a cell based on country selection hussainshaikh1 Excel 1 02-02-2017 07:06 AM
Splitting International Dialing codes into country and area code stefansfs Excel 1 08-17-2015 11:49 PM
Skipping USA as a country identifier Delta223 Mail Merge 1 04-03-2015 02:27 AM
"+" in Country code in Outlook 2010 musicaldoc Outlook 0 04-27-2013 02:19 AM
Adding a country drop-down list iyavor Word 0 10-27-2010 03:51 AM


All times are GMT -7. The time now is 06:55 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft