Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-24-2017, 08:56 PM
mikehk mikehk is offline VLOOKUP for country / country code Windows 10 VLOOKUP for country / country code Office 2013
Novice
VLOOKUP for country / country code
 
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 VLOOKUP for country / country code Windows 7 64bit VLOOKUP for country / country code Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 09-24-2017, 11:51 PM
mikehk mikehk is offline VLOOKUP for country / country code Windows 10 VLOOKUP for country / country code Office 2013
Novice
VLOOKUP for country / country code
 
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 VLOOKUP for country / country code Windows 7 64bit VLOOKUP for country / country code Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 09-25-2017, 01:53 AM
mikehk mikehk is offline VLOOKUP for country / country code Windows 10 VLOOKUP for country / country code Office 2013
Novice
VLOOKUP for country / country code
 
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 VLOOKUP for country / country code Windows 10 VLOOKUP for country / country code Office 2013
Novice
VLOOKUP for country / country code
 
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's Avatar
Debaser Debaser is offline VLOOKUP for country / country code Windows 7 64bit VLOOKUP for country / country code Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
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 VLOOKUP for country / country code Windows 10 VLOOKUP for country / country code Office 2013
Novice
VLOOKUP for country / country code
 
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
VLOOKUP for country / country code 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft