好的，假设上表从单元格A1开始，有用数据从第2行开始。这个公式将会诀窍：

  = INDEX（$ A $ 1：$ A $ 7，MAX IF（$ B $ 2：$ B2 = B2-1，ROW（$ B $ 2：$ B2），）））
 

在单元格 C2 中输入数组公式（Ctrl + Shift + Enter），然后将其拉下。第一个显然是一个错误（不是 #NA 但 #VALUE ）。

工作原理

  IF（$ B $ 2：$ B2 = B2-1，ROW（$ B $ 2：$ B2），）
 

一个数组，其行号为一个级别低于实际值的值。要仅查看当前行 之前的值，您需要使用展开范围，因此需要使用 $ B $ 2：$ B2 样式引用。 p>

MAX 函数获取最接近我们当前单元格的这些行的最大值。现在我们有行号。我们现在需要的是从指定行中从 A 列中提取数据的公式。这就是 INDEX 。

I currently have a sheet in excel with an indented hierarchy of items as shown below.
Each item is indented (four spaces per indent) to show how it fits into the overall hierarchy.
I have been able to create a "Level" column that translates the indentation level into a number.

    +------------+-------+--------+
    |    Item    | Level | Parent |
    +------------+-------+--------+
    | P1         |     1 | N/A    |
    |     P2     |     2 | P1     |
    |     P3     |     2 | P1     |
    |         P4 |     3 | P3     |
    |     P5     |     2 | P1     |
    |         P6 |     3 | P5     |
    +------------+-------+--------+

What I want to do is generate the "Parent" column above, which uses the "level" information to display each item's parent.
I think that this would need to be done with a loop that would do this for each item X :

    -Find level info for X
    -Find (levelx-1) which would equal the parent item's level
    -Search upward for the first row with a level equal to (levelx-1)
    -Find the item number in that row
    -Write item number in adjacent cell to X

Unfortunately, I'm not sure how to translate this idea into VBA.
Thanks in advance for any assistance.

OK, assuming the above table starts in cell A1, useful data starts in row 2. This formula will do the trick:

=INDEX($A$1:$A$7,MAX(IF($B$2:$B2=B2-1,ROW($B$2:$B2),"")))

Enter this in cell C2 as an array formula (Ctrl+Shift+Enter), then pull it down. The first one will obviously be an error (not #NA but #VALUE).

How it works:

IF($B$2:$B2=B2-1,ROW($B$2:$B2),"")

This creates an array with the row numbers for values with one level lower than the actual value. To examine only the values above the current row, you need to use expanding ranges, hence the $B$2:$B2 style references.

The MAX function gets the maximum of these rows, which is the closest to our current cell. Now we have the row number. All we need now is a formula to extract the data from column A from the indicated row. This is what INDEX does.