英文:
Vlookup formula to return a result which may not be the first instance
问题
我有一个表格，自动从外部来源导入数据，格式与以下类似。我正在尝试检索与特定ID关联的销售人员到另一个表格中。
我一直在尝试使用Vlookup，但它只检索它找到的第一个结果，通常是空白。
| 清单 | 客户ID | 销售人员 |
|---|---|---|
| 交付 | 124564 | |
| 交付 | 124564 | Gemma |
| 规划 | 56787 | Timothy |
| 规划 | 56787 | |
| 规划 | ||
| 丢失 | ||
| 丢失 | 43978 | Harry |
| 丢失 | 43978 |
我有另一个Vlookup参考表，其中我按以下方式组织了ID和销售人员以进行引用。
| 客户ID | 销售人员 |
|---|---|
| 124564 | Gemma |
| 56787 | Timothy |
| 43978 | Harry |
我试图将公式应用到的表格看起来像下面这样。
| 客户ID | 销售人员 |
|---|---|
| 124564 | =vlookup(客户ID,清单:销售人员,3,FALSE) |
| 56787 | |
| 43978 |
我不确定我是否表达清楚，但我基本上试图在具有相同引用项的表格中返回销售人员的姓名，他们可能是表格中的第三或第四个值。
英文:
I have a sheet that automatically imports data from an external source in a somewhat similiar format to the below. I'm trying to retrieve the sales person associated to a certain ID into another table.
I've been trying to use a Vlookup but it only retrieves the first result which it finds which is often the blank.
| List | client ID | Sales Person |
|---|---|---|
| delivery | 124564 | |
| delivery | 124564 | Gemma |
| Scoping | 56787 | Timothy |
| Scoping | 56787 | |
| Scoping | ||
| Lost | ||
| Lost | 43978 | Harry |
| Lost | 43978 |
I have another Vlookup reference table where I have the Id's and Sales Persons organised to reference such as the below.
| client ID | Sales Person |
|---|---|
| 124564 | Gemma |
| 56787 | Timothy |
| 43978 | Harry |
the table im trying to work the formula into looks something like the below.
| client ID | Sales Person |
|---|---|
| 124564 | =vlookup(client ID,List:Sales Person,3,FALSE) |
| 56787 | |
| 43978 |
I'm not sure if I've worded this well at all, but im essentially trying to return the name of the sales person when they may be the third or fourth value in the table with the same reference item..
答案1
得分: 2
你可以尝试：
=map(F2:F,lambda(Σ,if(Σ="","",iferror(+tocol(filter(C:C,B:B=Σ),1)))))
英文:
You may try:
=map(F2:F,lambda(Σ,if(Σ="",,iferror(+tocol(filter(C:C,B:B=Σ),1)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论