索引/匹配-如果第一个值为空白，则查找第二个值 [英] Index/Match- Lookup 2nd Value if 1st Value is Blank

问题描述

我希望在Excel中编写公式时能得到一些帮助. 我有一个表，其中包含员工及其手机号码的列表.但是，该表的结构方式使得存在许多空白行和重复行.

I was hoping to get some assistance in writing a formula in excel. I have a table that contains a list of employees and their cell phone number. However, the table is structured in such a way that there is a number of blank and duplicate rows.

本质上，我希望通过在各个手机号上进行查找来创建一个没有任何重复和空白的新表.

Essentially, I am hoping to create a new table without any duplicates and blanks by performing a lookup on the respective cell phone numbers.

问题是，当我执行标准的Index/Match公式时，该公式将仅获取第一个查找值，该值通常是空白行.

The problem is, when I do a standard Index/Match formula, the formula will grab just the first lookup value, which more often than not, is a blank row.

我将如何修改此公式以使其类似于查找该值.如果该值为空，请查找第二个值"?

How would I modify this formula to say something like, "Look this value up. If the value is blank, lookup the second value"?

以下是表格的示例:

Table 1 (Original)
    +---------------+--------------+
    | Employee Name |  Cell Phone  |
    +---------------+--------------+
    | Doe, John     |              |
    | Doe, John     | 111-111-1111 |
    | Smith, Eric   | 222-222-2222 |
    | Jones, Dave   |              |
    | Jones, Dave   | 333-333-3333 |
    +---------------+--------------+

Table 2 (What I want the table to look like)
    +---------------+--------------+
    | Employee Name |  Cell Phone  |
    +---------------+--------------+
    | Doe, John     | 111-111-1111 |
    | Smith, Eric   | 222-222-2222 |
    | Jones, Dave   | 333-333-3333 |
    +---------------+--------------+



=Index(Table1[Cell Phone], Match([Employee Name], Table1[Employee Name],0))

推荐答案

第一个非空白匹配如何?

How about the first non-blank match?

=INDEX(B:B, AGGREGATE(15, 6, ROW($2:$999)/((A$2:A$999=F2)*(B$2:B$999<>"")), 1))
'listobject table alternative
=INDEX(Table1[Cell Phone], AGGREGATE(15, 6, (ROW(Table1[Cell Phone])-ROW(Table1[#Headers]))/((Table1[Employee Name]=F7)*(Table1[Cell Phone]<>"")), 1))

这篇关于索引/匹配-如果第一个值为空白，则查找第二个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！