CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
ranopano
ranopano

Reputation: 539

Index/Match- Lookup 2nd Value if 1st Value is Blank

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.

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"?

Here is a sample of what the table looks like:

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))

Upvotes: 3

Views: 899

Answers (1)

user4039065
user4039065

Reputation:

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))

enter image description here

Upvotes: 2

Related Questions