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 online now Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,647
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,937
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: 135
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

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 10:39 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