Microsoft Office Forums VLOOKUP for country / country code
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-24-2017, 08:56 PM
 mikehk Windows 10 Office 2013 Novice Join Date: Sep 2017 Posts: 15
VLOOKUP for country / country code

I tried many formulas and either I get #VALUE or #N/A,

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

I have in Sheet 2

cell A15 | B15

Australia | 61

China | 86
#2
09-24-2017, 09:27 PM
 macropod Windows 7 64bit Office 2010 32bit Administrator Join Date: Dec 2010 Location: Canberra, Australia Posts: 18,273

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]
#3
09-24-2017, 11:51 PM
 mikehk Windows 10 Office 2013 Novice Join Date: Sep 2017 Posts: 15

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.
#4
09-24-2017, 11:56 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Moderator Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,148

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
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
#5
09-25-2017, 01:53 AM
 mikehk Windows 10 Office 2013 Novice Join Date: Sep 2017 Posts: 15

=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 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
#6
09-25-2017, 02:07 AM
 mikehk Windows 10 Office 2013 Novice Join Date: Sep 2017 Posts: 15

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
#7
09-25-2017, 03:35 AM
 Debaser Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Oct 2015 Posts: 155

You don't need 1* but you did need the comma in front of MATCH.
#8
09-25-2017, 04:05 AM
 mikehk Windows 10 Office 2013 Novice Join Date: Sep 2017 Posts: 15

Awesome!
Removed 1*

Thanks again.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post hussainshaikh1 Excel 1 02-02-2017 07:06 AM stefansfs Excel 1 08-17-2015 11:49 PM Delta223 Mail Merge 1 04-03-2015 02:27 AM musicaldoc Outlook 0 04-27-2013 02:19 AM iyavor Word 0 10-27-2010 03:51 AM

All times are GMT -7. The time now is 05:08 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top