VLOOKUP or MATCH formula to return True or False for list of countries

G

goldiekratz437

New Member
Joined
May 14, 2013
Messages
9
Dear Gurus:

OK, I've been trying to use a VLOOKUP or MATCH formula to determine if the country in column A is a Priority Market. Priority Markets is a tab with a list of 23 countries. I keep getting a #N/A error on all of the cells. I need to know which countries are the Priority Markets.

Here's a sample of the list and the formulas and my formula points to a tab in the workbook with the list of Priority Markets, e.g. Brazil, CACM, Canada, Columbia. All the countries are in column A on the tab.
[TABLE="width: 1177"]
<TBODY>[TR]
[TD]Country</SPAN>
[/TD]
[TD]Project Name</SPAN>
[/TD]
[TD]AN_PRODPRESENT</SPAN>
[/TD]
[TD]EMBR Number</SPAN>
[/TD]
[TD]Match</SPAN>
[/TD]
[TD]Vlookup</SPAN>
[/TD]
[/TR]
[TR]
[TD]Adria</SPAN>
[/TD]
[TD]AD SI SK Vital 1.5 RPB</SPAN>
[/TD]
[TD]200ml RPB</SPAN>
[/TD]
[TD]20130068</SPAN>
[/TD]
[TD]=MATCH(A2,'Priority Mkts'!A2:A24,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A2,'Priority Mkts'!A2:A24,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Argentina</SPAN>
[/TD]
[TD]Argentina-FSMP label change</SPAN>
[/TD]
[TD]Jevity Plus RTH 1000ml</SPAN>
[/TD]
[TD]20100186</SPAN>
[/TD]
[TD]=MATCH(A3,'Priority Mkts'!A3:A25,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A3,'Priority Mkts'!A3:A25,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Australia</SPAN>
[/TD]
[TD]Australia-Glucerna TC-Pwd FWP</SPAN>
[/TD]
[TD]sachet, 850g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A4,'Priority Mkts'!A43:A65,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A4,'Priority Mkts'!A43:A65,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Australia</SPAN>
[/TD]
[TD]Australia-Ensure Plus Juce Alt WPI Qual</SPAN>
[/TD]
[TD]220 ml Tetra</SPAN>
[/TD]
[TD]20120398</SPAN>
[/TD]
[TD]=MATCH(A5,'Priority Mkts'!A62:A84,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A5,'Priority Mkts'!A62:A84,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Austria</SPAN>
[/TD]
[TD]AT IT CH DE-Nepro HP & LP</SPAN>
[/TD]
[TD]HP Van 500ml RTH</SPAN>
[/TD]
[TD]10001480</SPAN>
[/TD]
[TD]=MATCH(A6,'Priority Mkts'!A63:A85,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A6,'Priority Mkts'!A63:A85,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Austria</SPAN>
[/TD]
[TD]Austria-Distal End Conversion</SPAN>
[/TD]
[TD]Phase 2 - Multiple Tube sets</SPAN>
[/TD]
[TD]20120353</SPAN>
[/TD]
[TD]=MATCH(A7,'Priority Mkts'!A64:A86,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A7,'Priority Mkts'!A64:A86,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Azerbaijan</SPAN>
[/TD]
[TD]CIS Azerbaijan – Wave II OL</SPAN>
[/TD]
[TD]Multiple</SPAN>
[/TD]
[TD]20120234</SPAN>
[/TD]
[TD]=MATCH(A8,'Priority Mkts'!A65:A87,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A8,'Priority Mkts'!A65:A87,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bahrain</SPAN>
[/TD]
[TD]Gulf, Levant - Similac Intelli-Pro</SPAN>
[/TD]
[TD]400g, 900g, 1.7kg</SPAN>
[/TD]
[TD]20120203</SPAN>
[/TD]
[TD]=MATCH(A9,'Priority Mkts'!A69:A91,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A9,'Priority Mkts'!A69:A91,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bahrain</SPAN>
[/TD]
[TD]Gulf-Nepro HP & LP</SPAN>
[/TD]
[TD]HP Vanilla 220 ml</SPAN>
[/TD]
[TD]10001480</SPAN>
[/TD]
[TD]=MATCH(A10,'Priority Mkts'!A75:A97,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A10,'Priority Mkts'!A75:A97,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Bahrain</SPAN>
[/TD]
[TD]Gulf-Ensure NutriVigor</SPAN>
[/TD]
[TD]Sachet, 400g, 850g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A11,'Priority Mkts'!A76:A98,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A11,'Priority Mkts'!A76:A98,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Belgium</SPAN>
[/TD]
[TD]Belgium-Distal End Conversion</SPAN>
[/TD]
[TD]phase 2 - multiple tube sets</SPAN>
[/TD]
[TD]20120353</SPAN>
[/TD]
[TD]=MATCH(A12,'Priority Mkts'!A78:A100,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A12,'Priority Mkts'!A78:A100,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Brazil</SPAN>
[/TD]
[TD]Brazil-Similac Intelli-Pro WPC35</SPAN>
[/TD]
[TD]Stage 3 400g, 850g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A13,'Priority Mkts'!A109:A131,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A13,'Priority Mkts'!A109:A131,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Brazil</SPAN>
[/TD]
[TD]Brazil-Similac Intelli-Pro WPC35</SPAN>
[/TD]
[TD]Stage 1 400g, 850g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A14,'Priority Mkts'!A110:A132,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A14,'Priority Mkts'!A110:A132,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Brazil</SPAN>
[/TD]
[TD]Brazil-Glucerna Calorically Dense Phase 2</SPAN>
[/TD]
[TD]1.5 kcal, Vanilla 500mL</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A15,'Priority Mkts'!A111:A133,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A15,'Priority Mkts'!A111:A133,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Brazil</SPAN>
[/TD]
[TD]Brazil-Nepro HP/LP</SPAN>
[/TD]
[TD]Nepro HP Vanilla Tetra 200mL</SPAN>
[/TD]
[TD]10001480 (20110023)</SPAN>
[/TD]
[TD]=MATCH(A16,'Priority Mkts'!A112:A134,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A16,'Priority Mkts'!A112:A134,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]CACM</SPAN>
[/TD]
[TD]CACM-Similac RS</SPAN>
[/TD]
[TD]375g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A17,'Priority Mkts'!A128:A150,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A17,'Priority Mkts'!A128:A150,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]CACM</SPAN>
[/TD]
[TD]CACM-RPB Expansion</SPAN>
[/TD]
[TD]Ensure Base Vanilla RPB 237ml</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A18,'Priority Mkts'!A129:A151,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A18,'Priority Mkts'!A129:A151,1,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Canada</SPAN>
[/TD]
[TD]Canada-Pediasure Peptide 1.0kcal</SPAN>
[/TD]
[TD]8oz bottle</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A19,'Priority Mkts'!A217:A239,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A19,'Priority Mkts'!A217:A239,AA19,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Canada</SPAN>
[/TD]
[TD]Canada-Pediasure Peptide 1.0kcal</SPAN>
[/TD]
[TD]8oz bottle</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A20,'Priority Mkts'!A221:A243,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A20,'Priority Mkts'!A221:A243,AA20,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Caribbean</SPAN>
[/TD]
[TD]Caribbean-IDB/POP</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD]=MATCH(A21,'Priority Mkts'!A222:A244,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A21,'Priority Mkts'!A222:A244,AA21,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chile</SPAN>
[/TD]
[TD]Chile-Misconnection Sets</SPAN>
[/TD]
[TD]FreeGo Device Set</SPAN>
[/TD]
[TD]20120353</SPAN>
[/TD]
[TD]=MATCH(A22,'Priority Mkts'!A223:A245,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A22,'Priority Mkts'!A223:A245,AA22,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Chile</SPAN>
[/TD]
[TD]Chile-Similac Mom</SPAN>
[/TD]
[TD]400g, Sachet</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A23,'Priority Mkts'!A225:A247,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A23,'Priority Mkts'!A225:A247,AA23,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]China</SPAN>
[/TD]
[TD]China-Similac Intelli-Pro Stage 1,2,3</SPAN>
[/TD]
[TD]Stage 3 Sachet, 900g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A24,'Priority Mkts'!A247:A269,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A24,'Priority Mkts'!A247:A269,AA24,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]China</SPAN>
[/TD]
[TD]China-CANPE Ensure NutriVigor Pwd (9g)</SPAN>
[/TD]
[TD]400g</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A25,'Priority Mkts'!A249:A271,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A25,'Priority Mkts'!A249:A271,AA25,FALSE)</SPAN>
[/TD]
[/TR]
[TR]
[TD]Colombia</SPAN>
[/TD]
[TD]Colombia-RPB Expansion</SPAN>
[/TD]
[TD]Pediasure TripleSure Vanilla RPB 237ml</SPAN>
[/TD]
[TD][/TD]
[TD]=MATCH(A26,'Priority Mkts'!A291:A313,0)</SPAN>
[/TD]
[TD]=VLOOKUP(A26,'Priority Mkts'!A291:A313,AA26,FALSE)

</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
I am getting the #N/A on all instead of FALSE on the non-priority markets.
I hope I'm explaining this good enough...

Thank you in advance for your help.

Jeanette
 
Sort by date Sort by votes
Hi Jeanette,

Try altering your vlookup to this:

Excel Workbook
ABCDE
1ArgentinaArgentina-FSMP label changeJevity Plus RTH 1000ml20100186Priority
2BrazilBrazil-Similac Intelli-Pro WPC35Stage 3 400g, 850gNon-Priority
Sheet1



Hope this helps,

AP
 
Upvote 0
Thank you for the formula, but now all the results are "Non-Priority." I'm not understanding why the priority mkts sheet reference changes:
=IF(ISERROR(VLOOKUP(I9,'Priority Mkts'!A8:A33,1,FALSE)),"Non-Priority","Priority"). My list has only 25 entries. I need the formula to check the worksheet and if one of the countries is in the list, it's a priority market. If not, it's a non-priority market.

Thank you for your help.
 
Upvote 0
OK, nevermind....I figured it out! I needed to put an absolute cell reference in the formula...then it worked.

Thanks for helping.

Jeanette
 
Upvote 0
You must log in or register to reply here.

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top