excel - INDEX 组合两列，其中一列跨越多行

Question

我的 INDEX 公式，基于 this answer to VLOOKUP by Combining 2 Columns to Form a Unique Key question在 MATCH 的 lookup_range 参数之一跨越多行的情况下不起作用:

=INDEX($C$3:$C$5, MATCH($A$3 & $B$3, $A$3:$A$5 & $B$3:$B$5, 0))
                                     ^       ^
                                     |       |
                                     +---+---+
                                         |
                      lookup_range column spans multiple rows

用法:

+---+------+-------+---------------+-----------------+----------------+
| \ |   A  |   B   |       C       |        D        |        E       |
+---+------+-------+---------------+-----------------+----------------+
| 1 |                               CARS                              |
+---+-----------------------------------------------------------------+
| 2 | Make | Model | Current price | Production Year | Original price |
+---+------+-------+---------------+-----------------+----------------+
| 3 | Audi | A1    | 40000         | 2013            | =MY_FORMULA    |
+---+------+-------+---------------+-----------------+----------------+
| 4 | Audi | A2    | 50000         | 2010            | =MY_FORMULA    |
+---+------+-------+---------------+-----------------+----------------+
| 5 | Audi | A3    | 60000         | 2014            | =MY_FORMULA    |
+---+------+-------+---------------+-----------------+----------------+

它确实适用于此表:

+---+------+-------+-------+
| \ |   A  |   B   |   C   |
+---+------+-------+-------+
| 1 |        Prices        |
+---+----------------------+
| 2 | Make | Model | Price |
+---+------+-------+-------+
| 3 | Audi | A1    | 50000 |
+---+------+-------+-------+
| 4 | Audi | A2    | 60000 |
+---+------+-------+-------+
| 5 | Audi | A3    | 70000 |
+---+------+-------+-------+

对于这个表不起作用(我希望它起作用):

+---+------+-------+-------+
| \ |   A  |   B   |   C   |
+---+------+-------+-------+
| 1 |        Prices        |
+---+----------------------+
| 2 | Make | Model | Price |
+---+------+-------+-------+
| 3 | Audi | A1    | 50000 |
+---+      +-------+-------+
| 4 |      | A2    | 60000 |
+---+      +-------+-------+
| 5 |      | A3    | 70000 |
+---+------+-------+-------+

很明显，将 AUDI 值跨越多行以避免冗余和拼写错误。我怎样才能实现这个目标？

Answer 1

您应该能够在 Google 表格中使用 vlookup 执行此操作，如下所示:-

=ArrayFormula(vlookup(E3&F3,{vlookup(row(A$3:A$10),{if(A$3:A$10<>"",row(A$3:A$10)),A$3:B$10},2)&B$3:B$10,C$3:C$10},2,false))

内部 vlookup 查找包含行号小于或等于当前行的汽车品牌的最后一行。如果您愿意，外部 vlookup 可以是索引匹配，并且您可以通过输入 A$3:A 等将范围扩展到最后一行。

<小时/>

为了完整起见，在 Excel 中，您必须引导 Index 函数为您提供一个可以处理的数组 see this

=INDEX(C$3:C$10,MATCH(E3&F3,INDEX(A$3:A$10,N(IF({1},MATCH(ROW(A$3:A$10),IF(A$3:A$10<>"",ROW(A$3:A$10))))))&B$3:B$10,0))

作为数组公式输入。