Excel失败在String VLOOKUP [英] Excel failing at String VLOOKUP
问题描述
我有一张这样的表格
BC
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!
更新我在另一个表中复制了这个确切的错误。
VLOOKUP
在搜索值为数字时工作, / div> VLOOKUP有奇怪的事情,除非你用第四个参数指定一个完全匹配,如下所示:
= VLOOKUP(S,B43:C48,2,FALSE)
从Excel的帮助文件: / p>
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
如果
range_lookup
为TRUE,table_array的第一列中的值必须按照升序排列:...,-2,-1,0,1,2,...,AZ ,FALSE,TRUE否则VLOOKUP
可能没有给出正确的值。如果range_lookup
为FALSE,table_array不需要排序。
还有:
range_lookup
是一个逻辑值,指定是否需要VLOOKUP
可以找到完全匹配或近似匹配。如果为TRUE或省略,则返回近似匹配。换句话说,如果找不到完全匹配,则返回小于lookup_value
的下一个最大值。如果FALSE
,VLOOKUP
将找到一个完全匹配。如果没有找到,则返回错误值#N / A
。
I have a table that looks like this
B C
43 XS 6
44 S 11
45 M 16
46 L 21
47 XL 26
48 XXL 31
I've written the formula:
`VLOOKUP("S",B43:C48,2)`
It's returning a value of 21. WHY?! It should be returning 11!
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 does strange things unless you specify an "exact match" with the fourth argument, like so:
=VLOOKUP("S",B43:C48,2,FALSE)
From Excel's help file:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
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; otherwiseVLOOKUP
may not give the correct value. Ifrange_lookup
is FALSE, table_array does not need to be sorted.
And also:
range_lookup
is a logical value that specifies whether you wantVLOOKUP
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 thanlookup_value
is returned. IfFALSE
,VLOOKUP
will find an exact match. If one is not found, the error value#N/A
is returned.
这篇关于Excel失败在String VLOOKUP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!