CODEWITHSUNDEEP
excelvbaexcel-formula
Catalin Vasilescu
Catalin Vasilescu

Reputation: 51

Replace values from a column based on other 2 columns

I have the following table:

Name1    Name   ID
123      Stefi  123   
Giorgi   Giorgi 858
211      Mara   211
Giorgi   Ana    333
Otto
Anna

The problem is that on the column Name1 I have both Name and Id and I need to have only the name. Based on the columns Name and Id I have to extract the wrong information from Name1 and replace with the name. Columns "Name" and "Id" can be seen as a separate table from where to extract information for the first column

At the end, the table should look like this

Name1    Name   ID
Stefi    Stefi  123   
Ana      Ana    333
Giorgi   Giorgi 858
Mara     Mara   211
Giorgi   
Otto
Anna

Any idea how I can fix this problem? Maybe with Index? Thanks!

Upvotes: 0

Views: 105

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

Use the following combination of INDEX and MATCH to get the result shown in column E Complete

  • Office 2013 or later use

    =IFNA(INDEX(B:B,MATCH(A:A,C:C,0)),A:A)

  • Office 2010 use

    =IFERROR(INDEX(B:B,MATCH(A:A,C:C,0)),A:A)

enter image description here

Upvotes: 1

Related Questions