excel vlookup_Excel VLOOKUP疑难解答

excel vlookup

You're admired by your co-workers, thanks to your awesome Excel skills. Cupcakes magically appear on your desk, in thanks for your help with complex formulas.

得益于您出色的Excel技能，您受到同事的钦佩。 纸杯蛋糕神奇地出现在您的办公桌上，以感谢您对复杂配方的帮助。

Your boss keeps stretching the budget, to accommodate the huge bonuses you get, in reward for your amazing talents. Well, that might not be the exact situation, but I'm sure your skills are appreciated!

您的老板不断增加预算，以容纳您获得的巨额奖金，以奖励您的才华。 好吧，这可能不是确切的情况，但是我相信您的技能会受到赞赏！

Then, one day, it all goes horribly wrong. Your boss needs a report in 15 minutes, and you can't get a seemingly simple VLOOKUP formula to work.

然后，有一天，一切都变得非常糟糕。 您的老板需要在15分钟内提交一份报告，并且您无法获得看似简单的VLOOKUP公式 。

You can see the target product numbers in the lookup table, but the formula result is #N/A, instead of the product price.

您可以在查找表中看到目标产品编号，但是公式结果为＃N / A，而不是产品价格。

You don't want to lose your Excel Expert badge over something this trivial, so how will you solve the problem?

您不想因为这些琐碎的事情而失去Excel Expert徽章，那么您将如何解决问题？

文字还是数字？ (Text or Number?)

A common cause for this VLOOKUP error is that one of the values is a number, and the other is text. In this example, the lookup table codes in cell B2:B5 are stored as text values – they have a leading apostrophe.

导致此VLOOKUP错误的常见原因是其中一个值是数字，另一个是文本。 在此示例中，单元格B2：B5中的查找表代码存储为文本值-它们具有前导撇号。

The lookup code, in cell B8, is entered as a number – no leading apostrophe.

在单元格B8中的查找代码以数字形式输入-无前导撇号。

So, it looks like cells B2 and B8 are equal, but Excel sees them as different values.

因此，看起来单元格B2和B8相等，但是Excel认为它们是不同的值。

修正文字值 (Fix the Text Values)

The easiest solution to the the VLOOKUP problem in this example is to convert the text values to numbers, so the codes in the table match your lookup values.

在此示例中，解决VLOOKUP问题的最简单方法是将文本值转换为数字 ，因此表中的代码与您的查找值匹配。

Or, type an apostrophe in front of your lookup code in cell B8, so it's a text value too.

或者，在单元格B8中的查找代码前面键入撇号，因此它也是文本值。

更改VLOOKUP公式 (Change the VLOOKUP Formula)

If you can't fix the data, you can convert the lookup value in the Excel VLOOKUP formula. Here is the original VLOOKUP formula, that returned an #N/A error.

如果无法修复数据，则可以在Excel VLOOKUP公式中转换查找值。 这是原始的VLOOKUP公式，返回了＃N / A错误。

=VLOOKUP(B8,$B$2:$D$5,2,FALSE)

= VLOOKUP(B8，$ B $ 2：$ D $ 5,2，FALSE)

If you add an empty string to the end of the value in cell B8, the lookup number will be converted to a text string. The revised formula is:

如果您在单元格B8中的值的末尾添加一个空字符串，则查找号将转换为文本字符串。 修改后的公式为：

=VLOOKUP(B8 & "",$B$2:$D$5,2,FALSE)

= VLOOKUP(B8＆“”，$ B $ 2：$ D $ 5,2，FALSE)

This formula will also work if cell B8 contains a text value – adding the empty string won't change the value.

如果单元格B8包含文本值，则此公式也将起作用-添加空字符串不会更改该值。

更多Excel VLOOKUP故障排除 (More Excel VLOOKUP Troubleshooting)

If this VLOOKUP formula fix doesn't solve the problem, there are more Excel VLOOKUP troubleshooting tips on the Contextures website. Have you run into this problem? How did you fix it?

如果此VLOOKUP公式修复不能解决问题，则Contextures网站上有更多Excel VLOOKUP故障排除提示 。 你遇到这个问题了吗？ 你怎么修好它的？

观看Excel VLOOKUP故障排除视频 (Watch the Excel VLOOKUP Troubleshooting Video)

To see the steps for fixing the Excel VLOOKUP problem, you can watch this short Excel video tutorial.

要查看解决Excel VLOOKUP问题的步骤，您可以观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/11/09/excel-vlookup-troubleshooting/

excel vlookup