Excel 在 String VLOOKUP 失败 [英] Excel failing at String VLOOKUP

问题描述

我有一张像这样的桌子

   B   C
43 XS  6
44 S   11
45 M   16
46 L   21
47 XL  26
48 XXL 31

我已经写好了公式:

`VLOOKUP("S",B43:C48,2)`

它返回的值为 21.为什么?！它应该返回 11！

It's returning a value of 21. WHY?! It should be returning 11!

更新 我在另一个表格中重现了这个确切的错误.VLOOKUP 在搜索值为数字时有效，但在我使用字符串时始终失败.

update I reproduced this exact error in a different table. VLOOKUP works when the search value is a number, but consistently fails when I use strings.

推荐答案

除非您指定完全匹配"，否则 VLOOKUP 会做一些奇怪的事情使用第四个参数，如下所示:

VLOOKUP does strange things unless you specify an "exact match" with the fourth argument, like so:

=VLOOKUP("S",B43:C48,2,FALSE)

来自 Excel 的帮助文件:

From Excel's help file:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

如果range_lookup为TRUE，table_array第一列的值必须按升序排列:..., -2, -1, 0, 1, 2, ...,AZ，假，真；否则 VLOOKUP 可能不会给出正确的值.如果 range_lookup 为 FALSE，则 table_array 不需要排序.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

还有:

range_lookup 是一个逻辑值，用于指定您希望 VLOOKUP 查找完全匹配还是近似匹配.如果为 TRUE 或省略，则返回近似匹配.换句话说，如果没有找到精确匹配，则返回小于 lookup_value 的下一个最大值.如果 FALSE，VLOOKUP 将找到完全匹配.如果未找到，则返回错误值 #N/A.

range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

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