excel - Excel 的 LOOKUP 或 VLOOKUP 查找文本的结果不正确

Question

假设我在一列中有 9 个引用单元格，每个单元格在下一列中都有对应的值，如下所示:

Alpha   1
Beta    2
Gamma   3
Delta   4
Epsilon 5
Zeta    6
Eta     7
Theta   8
Iota    9

我想使用 VLOOKUP 或 LOOKUP 进入引用单元格，并返回相应的值。
例如，如果在单元格 A1 中输入了“Alpha”，并且上述两列在 B 和 C 中，我将使用 =LOOKUP(A1, $B$1:$B$9, $C$1:$C$9)。哪个应该返回 1。但是，如果我对所有九个单元格都这样做，我会得到以下结果:

1
2
7
2
5
9
7
8
7

无论我将 =LOOKUP 与两个单独的向量或一个数组一起使用，还是使用 =VLOOKUP，我都会得到相同的结果。

任何有关为什么会发生这种情况的见解将不胜感激。

Answer 1

只需使用=VLOOKUP(A1,$B$1:$C$9,2,FALSE)始终使用 FALSE (或 0 )在公式中。我用 FALSE大多数时候在公式中，因为大多数时候数据没有排序，但是如果你已经知道你的数据是排序的，你可以使用 TRUE .

记住:

=VLOOKUP({a},{b},{c},{d})

a= 

lookup_value Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.

b =

table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

c = 

col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

d = 

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data in a range or table.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

简而言之:

=VLOOKUP({a},{b},{c},{d})

a = You want to find this
b = You want to find inside this
c = you want to return this column, Always a integer, and always is >1 and <total of columns of c
d = User FALSE or 0 if you have a unsorted list/data, and use TRUE if you ALREADY KNOW your data is sorted.