{"id":1106,"date":"2010-06-10T17:23:00","date_gmt":"2010-06-10T09:23:00","guid":{"rendered":"http:\/\/vm-officeblogs.cloudapp.net\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/"},"modified":"2022-07-26T04:17:42","modified_gmt":"2022-07-26T11:17:42","slug":"solutions-to-three-common-problems-when-using-vlookup","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/","title":{"rendered":"Solutions to Three Common Problems when Using VLOOKUP()"},"content":{"rendered":"<p><em>Today\u2019s author is Greg Truby, an Excel MVP, who addresses some common issues you may encounter when you use the VLOOKUP function.<\/em><\/p>\n<p>This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data. When using VLOOKUP() we frequently find ourselves facing three common problems:<\/p>\n<ul>\n<li>\n<div>We need to look up based on more than one column<\/div>\n<\/li>\n<li>\n<div>We\u2019re getting an #N\/A though the key is valid<\/div>\n<\/li>\n<li>\n<div>We\u2019re confronted with the Zoolander problem<\/div>\n<\/li>\n<\/ul>\n<p><strong>Problem #1: <\/strong>We need to look up something from a column, but we have to look up on two keys instead of one.\u00a0 Let\u2019s say we need to return the State information from the following table.<\/p>\n<table class=\"MsoNormalTable\" style=\"width: 307px; border-collapse: collapse;\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr style=\"height: 0.1in;\">\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; width: 48pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Produce <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; width: 48pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Color <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; border-right: #93cddd 1pt solid; width: 88pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">State <\/span><\/b><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Apples <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Red <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Washington <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Cherries <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Red <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Michigan <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Bananas <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Yellow <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Hawaii <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Lemons <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Yellow <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Texas <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Grapes <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Green <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">California <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Apples <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Green <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Pennsylvania <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Grapes <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Purple <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 88pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.75pt 0in 5.75pt;\" valign=\"bottom\" width=\"147\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">New York <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We cannot use just the Color column \u2013 would \u201cRed\u201d mean Washington?\u00a0 Or Michigan? We cannot use just the Produce column \u2013 would \u201cApples\u201d mean Washington? Or Pennsylvania?\u00a0 We need to look up on both Produce and Color.<\/p>\n<p><strong>Solution to Problem #1:<\/strong> The simplest solution is to create a helper column that combines both keys at once. To help readability we can insert some type of delimiter between the two fields such as the pipe (|), a comma or a semicolon. When we combine two or more pieces of text (which are frequently called \u201ctext strings\u201d or more simply \u201cstrings\u201d) we call this combining process \u201cconcatenation\u201d. The concatenation operator in Excel is the ampersand (&amp;) so our helper formula (using the pipe character) would be:<\/p>\n<p><span style=\"font-size: small;\"><span style=\"color: #000000;\">\u00a0<\/span><b><span style=\"color: #31849b;\">=A2 &amp; \u201d|\u201d &amp; B2<\/span> <\/b><\/span><\/p>\n<p>After inserting the helper column, the formula and copying down, we end up with the following table. Note that our composite column still needs to be to the left of the column whose data we will be returning.<\/p>\n<table class=\"MsoNormalTable\" style=\"width: 454px; border-collapse: collapse; margin: auto auto auto 4.8pt;\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr style=\"height: 0.1in;\">\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; width: 48.5pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Produce <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; width: 48pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Color <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; width: 107pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Composite <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #93cddd 1pt solid; height: 0.1in; border-right: #93cddd 1pt solid; width: 69.15pt; background: #4bacc6; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">State <\/span><\/b><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Apples <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Red <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Apples|Red <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Washington <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Cherries <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Red <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Cherries|Red <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Michigan <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Bananas <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Yellow <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Bananas|Yellow <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Hawaii <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Lemons <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Yellow <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Lemons|Yellow <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Texas <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Grapes <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Green <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Grapes|Green <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">California <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Apples <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Green <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Apples|Green <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Pennsylvania <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48.5pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left: #93cddd 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"81\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Grapes <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 48pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Purple <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 107pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"178\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: black;\">Grapes|Purple <\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #93cddd 1pt solid; border-top-color: #f0f0f0; width: 69.15pt; background: #dbeef3; border-bottom: #93cddd 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">New York <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>We can create a similar composite column to use inside the VLOOKUP() or we can do the concatenation inside the VLOOKUP() formula.<\/p>\n<p><strong>Problem #2:<\/strong> We know our data matches, but VLOOKUP() is returning #N\/A.<\/p>\n<p><strong>Solution #2:<\/strong> The problem is almost always that the keys are a mixture of numeric values and text values within the cells and one of the key columns is formatted as GENERAL and the other is formatted as TEXT. Here the left table\u2019s key is formatted as General and the right\u2019s key is formatted as Text.<\/p>\n<table class=\"MsoNormalTable\" style=\"width: 452px; border-collapse: collapse; margin: auto auto auto 4.8pt;\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr style=\"height: 15pt;\">\n<td style=\"border-top: #c2d69a 1pt solid; height: 15pt; width: 48pt; background: #9bbb59; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"color: white;\"><span style=\"font-size: small;\">Key <\/span><\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #c2d69a 1pt solid; height: 15pt; border-right: #c2d69a 1pt solid; width: 53pt; background: #9bbb59; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"color: white;\"><span style=\"font-size: small;\">Flowers <\/span><\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; border-right: white 1pt solid; border-top-color: #f0f0f0; width: 48pt; background: #9bbb59; border-bottom: white 1.5pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><b><span style=\"color: white;\"><span style=\"font-size: small;\">Key 2 <\/span><\/span><\/b><\/p>\n<\/td>\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 74pt; background: #9bbb59; border-bottom: white 1.5pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; text-indent: 11.05pt; line-height: normal;\"><b><span style=\"color: white;\"><span style=\"font-size: small;\">Vlookup() <\/span><\/span><\/b><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 15pt;\">\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 48pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">R <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-right: #c2d69a 1pt solid; border-top-color: #f0f0f0; width: 53pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Roses <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; border-right: white 1pt solid; border-top-color: #f0f0f0; width: 48pt; background: #d7e4bc; border-bottom: white 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">9 <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 74pt; background: #d7e4bc; border-bottom: white 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; text-indent: 11pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">#N\/A <\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 15pt;\">\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 48pt; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">4 <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-right: #c2d69a 1pt solid; border-top-color: #f0f0f0; width: 53pt; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Carnations <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; border-right: white 1pt solid; border-top-color: #f0f0f0; width: 48pt; background: #eaf1dd; border-bottom: white 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">4 <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 74pt; background: #eaf1dd; border-bottom: white 1pt solid; border-left-color: #f0f0f0; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; text-indent: 11pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">#N\/A <\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 15pt;\">\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 48pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">L <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-right: #c2d69a 1pt solid; border-top-color: #f0f0f0; width: 53pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Lilies <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; border-right: white 1pt solid; border-top-color: #f0f0f0; width: 48pt; background: #d7e4bc; border-left-color: #f0f0f0; border-bottom-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">R <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 74pt; background: #d7e4bc; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; text-indent: 11pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Roses <\/span><\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 15pt;\">\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 48pt; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">9 <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-right: #c2d69a 1pt solid; border-top-color: #f0f0f0; width: 53pt; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Zinnias <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; width: 74pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\"><\/td>\n<\/tr>\n<tr style=\"height: 15pt;\">\n<td style=\"height: 15pt; border-top-color: #f0f0f0; width: 48pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left: #c2d69a 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"color: black;\"><span style=\"font-size: small;\">T <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; border-right: #c2d69a 1pt solid; border-top-color: #f0f0f0; width: 53pt; background: #eaf1dd; border-bottom: #c2d69a 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"88\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"color: black;\"><span style=\"font-size: small;\">Tulips <\/span><\/span><\/p>\n<\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; width: 48pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"80\"><\/td>\n<td style=\"height: 15pt; width: 74pt; background-color: transparent; border-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"123\"><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>The first solution that will normally occur to us is \u201cI\u2019ll just format the General column as Text\u201d (or vice-versa).\u00a0 So we highlight one of the columns and hit Ctrl+F1 (or Home | Format | Format Cells (2007, 2010) or Format | Cells\u2026 (2003 &amp; below)) and change the format and \u2026. What!? It doesn\u2019t fix the problem. Changing a cell\u2019s format doesn\u2019t \u201ctake\u201d until you edit the cell.\u00a0 If we have more than just a few rows, we are not going to want to plink back and forth hitting F2 then ENTER a couple hundred times. We have two other options. One is to use Excel\u2019s built-in error correct if it has flagged this for us.\u00a0 In the screenshot below it has done so.\u00a0 We can then highlight all of the cells in that column and pick \u201cConvert to Number\u201d from the error correction popup menu.<\/p>\n<p>If we do not have the error correction option or if we simply prefer this method, we can use the <strong>Text to Columns<\/strong> tool instead. To use that we highlight the column whose format we wish to change and then from the menu (using Excel 2007 here) select Data | Text to Columns.<\/p>\n<p>We can just leave the DELIMITED option in place and click <strong>Next &gt;<\/strong> and then make sure that whatever delimiter we have checked does not actually occur in your column. Normally just sticking with TAB will work fine since it very rarely occurs in a cell.\u00a0 Then we click <strong>Next &gt;<\/strong> again and on Step 3 we pick the format we need General or Text and click Finish.<\/p>\n<p>Providing we correctly remembered what format we needed, we should be rewarded with the VLOOKUP() function working properly.<\/p>\n<p><strong>Alternate Solution<\/strong> \u2013 if we\u2019re feeling adventurous, we can do the type conversion in-formula by coercing the data types. If the cells that go into the first VLookup argument are formatted as TEXT and the keys that are in the range that are in the second argument are formatted as GENERAL then something like:<\/p>\n<p><span style=\"color: #000000;\"><span style=\"font-family: symbol;\"><span style=\"font-size: small;\">\u00b7<\/span><span style=\"font: 7pt 'Times New Roman';\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><span style=\"font-size: small;\">=VLOOKUP(<\/span><\/span><span style=\"font-size: small;\"><span style=\"color: blue;\">IF(ISERR(Key2+0),Key2,Key2+0)<\/span><span style=\"color: #000000;\">,<\/span><span style=\"color: #ff6600;\">Sheet1!Data<\/span><span style=\"color: #000000;\">,2,FALSE)\u00a0<\/span><\/span><\/p>\n<p>will coerce the first argument to GENERAL format whenever the data is numeric in nature.<\/p>\n<p>If it\u2019s the data that\u2019s in the key field of the 2nd argument\u2019s range that is TEXT format and our first argument\u2019s cell is GENERAL format then concatenating an empty string:<\/p>\n<p><span style=\"color: #000000;\"><span style=\"font-family: symbol;\"><span style=\"font-size: small;\">\u00b7<\/span><span style=\"font: 7pt 'Times New Roman';\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><span style=\"font-size: small;\">\u00a0<\/span><span style=\"font-size: small;\">=VLOOKUP(<\/span><\/span><span style=\"font-size: small;\"><span style=\"color: #009900;\">Key2&amp;\u201d\u201d<\/span><span style=\"color: #000000;\">,Sheet1!Data,2,FALSE) <\/span><\/span><\/p>\n<p>will coerce the numbers in the first argument to all text. [Note: in these examples the second argument is a <strong>named range<\/strong>.]<\/p>\n<p><strong>Next most likely solution<\/strong> &#8211; If the problem is not a mixture of text and numbers, then the next most common cause is extra spaces on one of the columns. One way we can quickly check this is by finding a cell that has returned #N\/A when it shouldn\u2019t and clicking on the key cell and hitting F2 and inspecting the cell\u2019s contents in the formula bar. If there are no leading or trailing spaces there, look at the corresponding key cell in the lookup range the same way. If we confirm that the problem is indeed being caused by leading or trailing spaces and if our data values have no spaces in the middle of them (our data reads \u201cExcelIsReallyFun\u00a0\u00a0\u00a0\u00a0 \u201d or \u201c\u00a0\u00a0\u00a0\u00a0 ExcelIsReallyRun\u201d; and not \u201c\u00a0 Excel Is Really Fun\u201d) then the simplest solution would be to just select the cells with too many spaces and hit Ctrl+H (Replace) and enter a space for the Find What argument and nothing at all for the Replace With argument.<\/p>\n<p>If there are spaces within the cells\u2019 values, one solution is to use the TRIM() function. If the extra spaces are in the first argument\u2019s cell, the solution is simple; we just insert TRIM in that first argument:<\/p>\n<p><span style=\"font-size: small;\"><span style=\"color: #000000;\"><span class=\"normalchar1\"><span style=\"font-family: symbol;\">\u00b7<\/span><\/span>\u00a0\u00a0\u00a0\u00a0 =VLOOKUP(TRIM(KEY),Sheet1!Data, 2, FALSE) <\/span><\/span><\/p>\n<p>If the extra spaces are in the lookup range then we can insert a helper column and use TRIM() in the helper column to eliminate leading and trailing spaces. We can then leave the helper column and change our VLOOKUP() to match on the helper column, or we can do a Copy and Paste Special\u2026 | Values over the top on the helper and then overwrite or delete the original column with the trimmed values. (The easiest way to access Paste Special regardless of version is to right-click once you have copied the cells.)<\/p>\n<p>If our data is always the same length after leading\/trailing spaces are removed then we could also use the Fixed Width option on the Text to Columns tool described above and split off any leading and trailing spaces and choose SKIP for the columns of extra spaces.<\/p>\n<p><strong>Problem #3:<\/strong> A member of a leading online Excel forum once quipped that he hated the fact that VLOOKUP() is \u201cZoolander challenged\u201d. In the movie <em>Zoolander<\/em>, Ben Stiller plays male model Derek Zoolander, who is not an \u201cambiturner\u201d. Derek can only turn right on the catwalk; he can\u2019t turn left. Likewise VLOOKUP() can only fetch back data from columns located to the right of the key, it cannot return from columns to the left. Let\u2019s say we need to return the type of flower given the one-letter key from the table below (we\u2019ll assume the table is located in A1:B6).<\/p>\n<table class=\"MsoNormalTable\" style=\"width: 218px; border-collapse: collapse;\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr style=\"height: 0.1in;\">\n<td style=\"border-top: #fac090 1pt solid; height: 0.1in; width: 69pt; background: #f79646; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><b><span style=\"font-size: 9pt; color: white;\">Flowers <\/span><\/b><\/p>\n<\/td>\n<td style=\"border-top: #fac090 1pt solid; height: 0.1in; border-right: #fac090 1pt solid; width: 62pt; background: #f79646; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><b><span style=\"font-size: 9pt; color: white;\">Key <\/span><\/b><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 69pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Roses <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #fac090 1pt solid; border-top-color: #f0f0f0; width: 62pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"font-size: 9pt; color: black;\">R <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 69pt; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Carnations <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #fac090 1pt solid; border-top-color: #f0f0f0; width: 62pt; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"font-size: 9pt; color: black;\">4 <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 69pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Lilies <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #fac090 1pt solid; border-top-color: #f0f0f0; width: 62pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"font-size: 9pt; color: black;\">L <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 69pt; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Zinnias <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #fac090 1pt solid; border-top-color: #f0f0f0; width: 62pt; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; background-color: transparent; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"font-size: 9pt; color: black;\">9 <\/span><\/p>\n<\/td>\n<\/tr>\n<tr style=\"height: 0.1in;\">\n<td style=\"height: 0.1in; border-top-color: #f0f0f0; width: 69pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left: #fac090 1pt solid; border-right-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"115\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal;\"><span style=\"font-size: 9pt; color: black;\">Tulips <\/span><\/p>\n<\/td>\n<td style=\"height: 0.1in; border-right: #fac090 1pt solid; border-top-color: #f0f0f0; width: 62pt; background: #fde9d9; border-bottom: #fac090 1pt solid; border-left-color: #f0f0f0; padding: 0in 5.4pt 0in 5.4pt;\" valign=\"bottom\" width=\"103\">\n<p class=\"MsoNormal\" style=\"margin: 0in 0in 0pt; line-height: normal; text-align: center;\" align=\"center\"><span style=\"font-size: 9pt; color: black;\">T <\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><strong>Solution #3:<\/strong> The way to return data from the left is to understand two additional functions, INDEX() and MATCH().\u00a0 The MATCH() function also looks something up for us. But instead of returning what\u2019s in the cell, it tells us the position in the list where it matches. The formula =MATCH(\u201cL\u201d,B1:B6,0) will return 4.<\/p>\n<p>The INDEX function returns the value of the contents of a range based on position. So the formula =INDEX(A1:A6, 4) will return \u201cLilies\u201d.<\/p>\n<p>So if we set that MATCH() inside the INDEX()<\/p>\n<p><span style=\"color: #000000;\"><span style=\"font-family: symbol;\"><span style=\"font-size: small;\">\u00b7<\/span><span style=\"font: 7pt 'Times New Roman';\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/span><\/span><span style=\"font-size: small;\">=<\/span><\/span><span style=\"font-size: small;\"><span style=\"color: #990099;\">INDEX(A1:A6,<\/span><span style=\"color: blue;\">MATCH(\u201cL\u201d,B1:B6,0)<\/span><span style=\"color: #990099;\">)<\/span> <\/span><\/p>\n<p>We now have a formula that can look left and return \u201cLilies\u201d given the input \u201cL\u201d.<\/p>\n<p>Unfortunately, MATCH() suffers the same inability to convert between numbers and text as VLOOKUP(), so if we have a mix of General and Text, we need to use the same Text to Columns or in-formula coercions as described in Solution #2.<\/p>\n<p><strong>Bonus<\/strong> \u2013 Once we become adept at using the team of INDEX() and MATCH() together, we can also employ them in an alternate solution to our composite key problem.\u00a0 Instead of inserting a helper column, we can have Excel do the concatenation inside the formula by entering the formula as an array formula.<\/p>\n<p>We type the formula in without the braces \u2013 the \u201c{\u201c and \u201c}\u201d \u2013 but instead of confirming the formula with a simple ENTER keystroke, we confirm it with CTRL+SHIFT+ENTER. To gain a better understanding of how the array formula actually works we can use the Evaluate Formula tool on the Formula tab in Excel 2007 &amp; 2010, or from the <strong>Tools, Formula Auditing, Evaluate Formula\u2026<\/strong> path in Excel 2003. I leave it to the reader to do this exploration.<\/p>\n<p>In summary we now have solutions to three of our most frequent challenges in using the VLOOKUP() function:<\/p>\n<ul>\n<li>We can now look up based on more than one column.<\/li>\n<li>We can troubleshoot cells where we\u2019re getting an #N\/A though the key is valid.<\/li>\n<li>We are now \u201cambiturners\u201d \u2013 we can fetch data from columns to the left of our key column.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.<\/p>\n","protected":false},"author":64,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ep_exclude_from_search":false,"_classifai_error":"","footnotes":""},"content-type":[4035],"product":[4047,4036],"audience":[4081,4101,4102],"tags":[],"coauthors":[4437],"class_list":["post-1106","post","type-post","status-publish","format-standard","hentry","content-type-tips-and-guides","product-excel","product-microsoft-365","audience-enterprise","audience-personal-and-family","audience-small-business"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v23.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog<\/title>\n<meta name=\"description\" content=\"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog\" \/>\n<meta property=\"og:description\" content=\"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft 365 Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-06-10T09:23:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-26T11:17:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/cropped-microsoft_logo_element.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"512\" \/>\n\t<meta property=\"og:image:height\" content=\"512\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"The Microsoft 365 Marketing Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"The Microsoft 365 Marketing Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 min read\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\"},\"author\":[{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/author\/the-microsoft-365-marketing-team\/\",\"@type\":\"Person\",\"@name\":\"The Microsoft 365 Marketing Team\"}],\"headline\":\"Solutions to Three Common Problems when Using VLOOKUP()\",\"datePublished\":\"2010-06-10T09:23:00+00:00\",\"dateModified\":\"2022-07-26T11:17:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\"},\"wordCount\":1719,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\",\"name\":\"Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website\"},\"datePublished\":\"2010-06-10T09:23:00+00:00\",\"dateModified\":\"2022-07-26T11:17:42+00:00\",\"description\":\"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Solutions to Three Common Problems when Using VLOOKUP()\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\",\"name\":\"Microsoft 365 Blog\",\"description\":\"Microsoft 365 brings together Office 365, Windows 10, and Enterprise Mobility + Security. It delivers a complete, intelligent, and secure solution to empower people.\",\"publisher\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization\",\"name\":\"Microsoft 365 Blog\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png\",\"contentUrl\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png\",\"width\":279,\"height\":60,\"caption\":\"Microsoft 365 Blog\"},\"image\":{\"@id\":\"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog","description":"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/","og_locale":"en_US","og_type":"article","og_title":"Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog","og_description":"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.","og_url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/","og_site_name":"Microsoft 365 Blog","article_published_time":"2010-06-10T09:23:00+00:00","article_modified_time":"2022-07-26T11:17:42+00:00","og_image":[{"width":512,"height":512,"url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/cropped-microsoft_logo_element.webp","type":"image\/png"}],"author":"The Microsoft 365 Marketing Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"The Microsoft 365 Marketing Team","Est. reading time":"7 min read"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#article","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/"},"author":[{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/author\/the-microsoft-365-marketing-team\/","@type":"Person","@name":"The Microsoft 365 Marketing Team"}],"headline":"Solutions to Three Common Problems when Using VLOOKUP()","datePublished":"2010-06-10T09:23:00+00:00","dateModified":"2022-07-26T11:17:42+00:00","mainEntityOfPage":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/"},"wordCount":1719,"commentCount":0,"publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/","name":"Solutions to Three Common Problems when Using VLOOKUP() | Microsoft 365 Blog","isPartOf":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website"},"datePublished":"2010-06-10T09:23:00+00:00","dateModified":"2022-07-26T11:17:42+00:00","description":"This article assumes a basic familiarity with the VLOOKUP() function, one of the easiest ways to lookup up a key value in one worksheet or block of data and return a related piece of information from a second worksheet or block of data.","breadcrumb":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/2010\/06\/10\/solutions-to-three-common-problems-when-using-vlookup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/"},{"@type":"ListItem","position":2,"name":"Solutions to Three Common Problems when Using VLOOKUP()"}]},{"@type":"WebSite","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#website","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/","name":"Microsoft 365 Blog","description":"Microsoft 365 brings together Office 365, Windows 10, and Enterprise Mobility + Security. It delivers a complete, intelligent, and secure solution to empower people.","publisher":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#organization","name":"Microsoft 365 Blog","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png","contentUrl":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-content\/uploads\/sites\/2\/2019\/08\/ms-logo-amp.png","width":279,"height":60,"caption":"Microsoft 365 Blog"},"image":{"@id":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/#\/schema\/logo\/image\/"}}]}},"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Microsoft 365 Blog","distributor_original_site_url":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts\/1106"}],"collection":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/users\/64"}],"replies":[{"embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/comments?post=1106"}],"version-history":[{"count":0,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/posts\/1106\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/media?parent=1106"}],"wp:term":[{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/content-type?post=1106"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/product?post=1106"},{"taxonomy":"audience","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/audience?post=1106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/tags?post=1106"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.microsoft.com\/en-us\/microsoft-365\/blog\/wp-json\/wp\/v2\/coauthors?post=1106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}