#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
=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 (. |
#6
|
|||
|
|||
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
|
||||
|
||||
You don't need 1* but you did need the comma in front of MATCH.
|
#8
|
|||
|
|||
Awesome!
Removed 1* Thanks again. |
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 |