根据“查找的"单元格的原始位置查找相邻的单元格值，同时处理重复的值 [英] Find adjacent cell values based on 'looked up' cells' original position whilst dealing with duplicate values

问题描述

我目前遇到了一个Excel问题，但似乎找不到答案.

I'm currently stuck with an excel problem that I can't seem to find an answer to.

采用以下示例数据集:

A          B          C          D          E          F          G     
Date       Color                                       Lookup     Adjacent value
1-1-2017   Green                                       3-5-2018
3-5-2018   Yellow                                      4-7-2018 
4-7-2018   Green                                       9-9-2018
1-2-2016   Purple                                      3-5-2018
6-9-2014   Red
9-9-2018   Green
3-5-2018   Blue

A和B列中的值是硬编码的.

Values in column A and B are hardcoded.

已使用以下公式检索F列中的值，该公式基于2018年1月1日至2020年12月31日之间的日期过滤"主数据(在我的实际数据集中，A列和B列在不同的位置表格中查找值和所需的相邻值):

Values in column F have been retrieved with the following formula that 'filters' the main data based on dates between 1-1-2018 and 31-12-2020 (in my real dataset column A and B will be on a different sheet than the lookup values and the required adjacent values):

=IFERROR(INDEX($A$1:$A$8;AGGREGATE(15;6;ROW($A$1:$A$8)/(($A$1:$A$8>=DATE(2018;1;1))*($A$1:$A$8<=DATE(2021;1;1)));ROW($A1)));"")

我现在需要找到一种方法，根据F列中的查找值从B列中检索相邻的单元格值(例如颜色)，并将其放置在G列中.然后，数据集应如下所示:

I now need to find a way to retrieve the adjacent cell values from column B (e.g. color) based on the lookup values in column F and place them in column G. The dataset should then look as follows:

A          B          C          D          E          F          G     
Date       Color                                       Lookup     Adjacent value
1-1-2017   Green                                       3-5-2018   Yellow
3-5-2018   Yellow                                      4-7-2018   Green
4-7-2018   Green                                       9-9-2018   Green
1-2-2016   Purple                                      3-5-2018   Blue
6-9-2014   Red
9-9-2018   Green
3-5-2018   Blue

保持序列完整很重要；即使A列包含重复的3-5-2018，但由于颜色不同，它们也不相同.

It is important for the sequence to remain intact; even though column A contains a duplicate of 3-5-2018 they are not the same due to their color being different.

我的主要问题:如何将正确的颜色附加到正确的查找日期值?

My main question: How do I attach the correct color to the correct lookup date value?

非常感谢您的帮助！

亲切的问候，

VHes

推荐答案

首先，您需要将索引函数数组从 $ A $ 1:$ A $ 8 更改为 $ B $ 2:$B $ 8 .尝试下面的公式.

First you need to change array of index function from $A$1:$A$8 to $B$2:$B$8. Try below formula.

=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8=F2),COUNTIF($F$2:$F2,F2))),"")

这篇关于根据“查找的"单元格的原始位置查找相邻的单元格值，同时处理重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！