Excel VLOOKUP错误的值 [英] Excel VLOOKUP gives wrong value

问题描述

我有一个VLookup单元，给我错误的值：
这是表：

I have a VLookup cell which gives me the wrong value: This is the table:

    PID Product Price   User    User name   Deal On Amount After
in  1001    table   1001    1   Milly   No  1000
in  1001    table   100 13  Vernetta    Yes 900
out 1001    table   50  14  Mireya  No  900
out 1001    table   100 15  Rosanne Yes 1000
out 1001    table   101 16  Belinda No  1000
in  1001    table   200 1   Milly   Yes 800
in  1234    chair   300 2   Charlena    Yes 500
in  1234    chair   100 3   Mina    Yes 400
in  1234    chair   50  4   Sabina  Yes 350
in  8231    couch   20  5   Joni    Yes 330
in  1001    table   150 6   Armando Yes 180
in  1001    table   100 7   Noemi   Yes 80
in  8231    couch   40  8   Ashlie  Yes 40
in  8231    couch   30  9   Ann Yes 10
out 1001    table   201 10  Angelina    Yes 211
out 1234    chair   300 11  Melvina Yes 511
out 8231    couch   21  12  Mattie  Yes 532

产品列是具有以下公式的VLOOKUP：

The product column is a VLOOKUP with the following formula

VLOOKUP(B6,$L$2:$M$10, 2)

B是PID列。 L2：M10中的表格如下：

B is the PID column. The table in L2:M10 is the following:

PID Prodcut
1001    table
1234    chair
8231    desk
2311    closet
9182    book_shelf
1822    bed
1938    coffee_book_table
2229    couch

现在你可以看到。 PID 8231是一张桌子，但它看起来像一个沙发。你可以看到问题是什么吗？

Now as you can see. PID 8231 is a desk, but it appears as a couch. Can you see what the problem is?

推荐答案

你的问题的关键是你写的公式的方式，你忘了最后一个参数FALSE或0表示您想要一个EXACT MATCH。所以你的公式应该如下所示：

The crux of your problem is the way you've written the formula, you forgot the last parameter, "FALSE" or 0 which means you want an EXACT MATCH. So your formula should look like this:

VLOOKUP(B6, $L$2:$M$10, 2, FALSE)

或

VLOOKUP(B6, $L$2:$M$10, 2, 0)

相同的东西。

它的默认设置为TRUE，它查找最接近的匹配，只要您查找的索引被排序，您只需要。

The default setting for it is TRUE, which looks for the closest match, which you only want if the index you're looking up off of is sorted.

这篇关于Excel VLOOKUP错误的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！