不使用 VBA 的条件 Vlook up [英] Conditional Vlook up without using VBA
问题描述
我想将输入转换为所需的输出.请帮助.在输出中 - 列值应从最近(年)开始
I want to convert an input to desired output. Kindly help. In the output - the columns value should start from most recent (year)
推荐答案
遗憾的是 VLOOKUP 无法满足该要求.但是 INDEX 函数可以.
Unfortunately VLOOKUP is not able to fulfill that ask. However the INDEX-function can.
这是关于如何使用它的好读物:http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list
Here is a good read on how to use it: http://fiveminutelessons.com/learn-microsoft-excel/use-index-lookup-multiple-values-list
如果您的输入表从 A1 开始没有标题，而您的输出表从 H3 开始并带有第一个 ID，这将适用于您的电子表格.
This will work for you spreedsheet, if your input table starts at A1 without a header and your output table starts at H3 with the first ID.
您可以通过将输入表的第一列复制并粘贴到 H 列，然后删除重复项来获得此功能.
You get this by copy&pasting the first column of your input table to column H and then remove duplicates.
{=IF(ISERROR(INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3)),"",
INDEX($A$1:$C$7;SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3))}
<小时>
让我们一步一步看公式:
Let's look at the formula step by step:
大括号告诉 excel 这是一个数组公式，对你来说有趣的部分是:当你插入公式(没有大括号)按 shift+ctrl+enter 时，excel 就会知道这是一个数组公式.
The curly brackets tell excel that this is an array formula, the interesting part for you is: when you've inserted the formula (without curly brackets) press shift+ctrl+enter, excel will then know that this is an array formula.
'error at formula?, then blank, else formula
=IF(ISERROR(....),"",...)
当您自动填充这个公式时，您可能不知道您的查找变量有多少个实例.所以当你把这个公式放在 4 个单元格中，但只有 3 个条目时，这个位会让单元格保持空白而不是给出错误.
When you autofill this formula you probably dont know how many instances of your lookup variable are. So when you put this formula in 4 cells, but there are only 3 entries, this bit will keep the cell blank instead of giving an error.
INDEX($A$1:$C$7,SMALL(IF($A$1:$A$7=$H$3,ROW($A$1:$A$7)),ROW(1:1)),3))
$A$1:$C$7 是您的数据矩阵.您的 ID(在您的情况下为 125 和 501)可以在 $A$1:$A$7 中找到.ROW(1:1) 是绝对 (!) 行 ID，3 是绝对 (!) 列 ID.因此，当您移动输入表时，必须更改这些值.
$A$1:$C$7 is your data matrix. Your IDs (in your case 125 and 501) are to be found in $A$1:$A$7. ROW(1:1) is the absolute(!) rowID, 3 the absolute(!) column id. So when you move your input table those values have to be changed.
上面的链接详细描述了 SMALL 和 INDEX 的作用.(或者至少比我能做到的更好.)
What exactly SMALL and INDEX do are well described in the link above. (Or at least better than I could.)
希望澄清一些部分，
汤姆
这篇关于不使用 VBA 的条件 Vlook up的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
