为什么VLOOKUP无法从右向左运行? [英] Why can't VLOOKUP function right-to-left?

问题描述

我知道您可以使用INDEX/MATCH来执行此操作，但是VLOOKUP无法执行此操作是出于逻辑还是特定原因?

I know you can use INDEX/MATCH to do this, but is there a logical or specific reason why VLOOKUP cannot do this?

也许只有Microsoft知道，但是看起来很简单，如果可以完成，那就可以了.

Maybe only Microsoft knows, but it seems simple enough that if it could have been done, it would have.

为澄清起见，请注意以下用于解释我所解释的差异的公式.

to clarify, note the following formulas for the difference I am explaining.

= VLOOKUP(A1，A:C，3，false)-有效
= VLOOKUP(C1，C:A，3，false)-无效

=VLOOKUP(A1,A:C,3,false) - valid
=VLOOKUP(C1,C:A,3,false) - not valid

推荐答案

实际上，可以从右向左进行vlookup，但是您需要重新定义数组.像这样:

Actually it is possible to do a right to left vlookup but you need to redifne your array. like this:

(A)      (B)    (C)
0,457   3,55    500
0,525   3,25    400
0,616   2,93    300
0,675   2,75    250
0,746   2,57    200
0,835   2,38    150
0,946   2,17    100
1,09    1,95    50
1,29    1,71    0

因此，这是一个公式，当要求在A:A，C:C数组中查找C1时，将提取A1.

so here is a formula which fetches A1 when asked to lookup C1 in the array of A:A, C:C.

=VLOOKUP(C1;CHOOSE({2\1};A:A;C:C);2;FALSE)

基本上，您需要重新定义查找数组，所以我选择了一个选择"公式， 设置一个表数组，其中列1为C:C，列2为A:A. 然后vLookup公式从C:C查找表数组的第1列的lookup_Value，然后在C1中找到500，然后转到A:A(表数组的第2列)，并从A1中提取相应的值是0,457.
P.S. 根据您所在的国家/地区编码系统，您需要替换所有;"与，"和"\"与，".
您可以下载此文件并在您的excel中打开它，然后看看excel如何翻译这些;" "\"进入您所在国家的编码系统.

Basically you need to redefine your lookup array, so I go for a 'choose' formula which sets up a table array where Column 1 is C:C and Column 2 is A:A. Then vLookup formula looks up the lookup_Value from C:C which is column 1 of the Table array and then locates 500 in C1 and then accordingly goes to A:A (column 2 of the table array) and pulls out the corresponding value from A1 which is 0,457.
P.S. depending on your country coding system, you need to replace all ";" with "," and "\" with ",".
You may download this file and open it in your excel and see how your excel translates these ";" "\" into your country coding system.

这篇关于为什么VLOOKUP无法从右向左运行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！