Find a match in one column with other column and then replace it with a value

C

cerebral

New Member
Joined
Jun 16, 2015
Messages
9
Hello,

I am trying to match Phone numbers from Customers sheet to the records in Calls_Logs sheet. I want the name in Call Logs sheet to get updated according to matching phone number. I have close to 300000 records so it is impossible for me to do it manually. I tried using this code: =VLOOKUP(B3,Patient!A:C,2,FALSE) but it is not able to find out. Please some one help me out

Customers sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Other Phone[/TD]
[TD]Work Phone[/TD]
[TD]Home Phone[/TD]
[/TR]
[TR]
[TD]Mysterio[/TD]
[TD]Rey[/TD]
[TD]7864866789[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steiner[/TD]
[TD]Scott[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]9546329085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]3972598[/TD]
[/TR]
[TR]
[TD]Cold[/TD]
[TD]Stone[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]78630585356[/TD]
[/TR]
[TR]
[TD]Lisa [/TD]
[TD]Ray[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]3619455588[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78, align: right"]2249332353[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Call_Logs Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Phone Number[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Duration[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]3972598[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112"]Mon 05/11/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]3:51 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]0:02:51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112"]Fri 04/24/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]4:27 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]0:01:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]7864866789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="width: 112"]Tue 03/31/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]12:02 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]0:03:53[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
What exactly do you want to return, the Last Name? First Name? A combination of both so that it returns "Stone Cold" ?

And also I'm assuming you want to lookup the phone number that matches anywhere in the three columns that have phone numbers on the Customers sheet?

Give us the correct names of your sheets (is it "Patients" or "Customers") and the columns too... is your Customers table in columns A:E or is it B:F or something else?
 
Upvote 0
Sorry it was my mistake I should have added everything in those two sheets.
Actually the sheets name is patients not customers. Right now I would love to return First name and in the column next to it second name.
Ya your are right I am looking for a phone number that matches in any of those columns in Customer/Patient table and return that matching name in Call_Logs table. Actually my Customers or Patients column is in A:E. Hope I explained it correctly this time and sorry for the ambiguity as I was not sure of how to present this question.

Patients sheet
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Other Phone[/TD]
[TD]Work Phone[/TD]
[TD]Home Phone[/TD]
[/TR]
[TR]
[TD]Mysterio[/TD]
[TD]Rey[/TD]
[TD]7864866789[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steiner[/TD]
[TD]Scott[/TD]
[TD][TABLE="class: cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]9546329085[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD]3972598[/TD]
[/TR]
[TR]
[TD]Cold[/TD]
[TD]Stone[/TD]
[TD][TABLE="class: cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD]78630585356[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Ray[/TD]
[TD][TABLE="class: cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]3619455588[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 78"]
<tbody>[TR]
[TD="width: 78, align: right"]2249332353[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Call_Logs Sheet
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Phone Number[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Duration[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]3972598[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Mon 05/11/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]3:51 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]0:02:51[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Fri 04/24/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]4:27 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]0:01:18[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]7864866789[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Tue 03/31/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]12:02 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, align: right"]0:03:53[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Sorry it was my mistake I should have added everything in those two sheets.
Actually the sheets name is patients not customers. Right now I would love to return First name and in the column next to it second name.
Ya your are right I am looking for a phone number that matches in any of those columns in Customer/Patient table and return that matching name in Call_Logs table. Actually my Customers or Patients column is in A:E. Hope I explained it correctly this time and sorry for the ambiguity as I was not sure of how to present this question.

Patients sheet
[TABLE="class: cms_table_cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Other Phone[/TD]
[TD]Work Phone[/TD]
[TD]Home Phone[/TD]
[/TR]
[TR]
[TD]Mysterio[/TD]
[TD]Rey[/TD]
[TD]7864866789[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steiner[/TD]
[TD]Scott[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]9546329085[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD]3972598[/TD]
[/TR]
[TR]
[TD]Cold[/TD]
[TD]Stone[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD]78630585356[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Ray[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 88"]
<tbody>[TR]
[TD="width: 88, align: right"]3619455588[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 78"]
<tbody>[TR]
[TD="width: 78, align: right"]2249332353[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Call_Logs Sheet
[TABLE="class: cms_table_cms_table_grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Phone Number[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Duration[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table_cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]3972598[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Mon 05/11/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]3:51 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]0:02:51[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table_cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]6612628976[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Fri 04/24/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]4:27 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]0:01:18[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table_cms_table, width: 104"]
<tbody>[TR]
[TD="width: 104, align: right"]7864866789[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 112"]
<tbody>[TR]
[TD="width: 112"]Tue 03/31/2015[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]12:02 PM[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="class: cms_table_cms_table, width: 64"]
<tbody>[TR]
[TD="class: cms_table_cms_table_xl65, width: 64, align: right"]0:03:53[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0
This ugly formula will work... but I actually just now realized the MATCH function only works across one row or column. Can't MATCH across multiple columns. This is a workaround for now. This will return the first name that matches the phone number in either C, D, or E on the Patients page, so enter it into E2 on your Call_Logs sheet:

=IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!E:E,0)),"")))

Then in F2 do the same thing but just change the "Patients!B:B" to "Patients!A:A" to find last name. Might not want to refer to the entire columns though like "B:B" for example, just do B1:B1000 or however far your data goes down.
 
Upvote 0
svendiamond said:
This ugly formula will work... but I actually just now realized the MATCH function only works across one row or column. Can't MATCH across multiple columns. This is a workaround for now. This will return the first name that matches the phone number in either C, D, or E on the Patients page, so enter it into E2 on your Call_Logs sheet:

=IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!E:E,0)),"")))

Then in F2 do the same thing but just change the "Patients!B:B" to "Patients!A:A" to find last name. Might not want to refer to the entire columns though like "B:B" for example, just do B1:B1000 or however far your data goes down.
Click to expand...

Thank you so much for sending this code, can you by any chance explain this code to me it will help me in understanding the code.
 
Upvote 0
Hello, I tried using that formula but it is giving me and error. I should make changes wherever B:B C:C D:D E:E is there or should I just make changes in B:B ?
Thank you.
 
Upvote 0
Sorry, just the first part the "B:B" because that is what you are going to be returning a value from. So:

To return first name, in E2 and fill down: =IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX (Patients!B:B,MATCH(A2,Patients!E:E,0)),"")))

To return last name, in F2 and fill down: =IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!E:E,0)),"")))

The formula simply does an INDEX/MATCH lookup (searching column C for a matching phone number) and if there is an error (no returned value) then it will go on to the next INDEX/MATCH lookup (searching column D for a matching phone number), and if that doesn't return anything, it tries the last lookup (searching column E for a matching phone number).
 
Upvote 0
svendiamond said:
Sorry, just the first part the "B:B" because that is what you are going to be returning a value from. So:

To return first name, in E2 and fill down: =IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX (Patients!B:B,MATCH(A2,Patients!E:E,0)),"")))

To return last name, in F2 and fill down: =IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!E:E,0)),"")))

The formula simply does an INDEX/MATCH lookup (searching column C for a matching phone number) and if there is an error (no returned value) then it will go on to the next INDEX/MATCH lookup (searching column D for a matching phone number), and if that doesn't return anything, it tries the last lookup (searching column E for a matching phone number).
Click to expand...

Hello Hi,

The code has started working but it is returning all the wrong values. Should the phone numbers be arranged in any particular format for this to work? There people in the patients sheet who have similar last name, and some people have even put same phone numbers in two different categories
 
Last edited:
Upvote 0
Are you using merged cells in your data? I'm trying it right now and it works perfectly... but when I copied and pasted your example table from your original post into Excel, some cells are merged... in a seemingly random pattern... this will make it very difficult to find the data you're looking for and I see no advantage in merging cells... ever, really.

These formulas work to return first and last name:

=IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!B:B,MATCH(A2,Patients!E:E,0)),"")))

=IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!C:C,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!D:D,0)),IFERROR(INDEX(Patients!A:A,MATCH(A2,Patients!E:E,0)),"")))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
536
skyh3ck
skyh3ck
S
  • Question Question
How to Distribute Data in a column/s to other sheets in terms of certain Cell Reference
Replies
0
Views
560
SalamaM
S
M
  • Question Question
Search Text file for cell value then replace regex match in text file from cell value 7 columns over from first match
Replies
1
Views
1K
meirbabboon
M
V
  • Question Question
Compare two columns in one workbook with two columns in another work book and return values
Replies
1
Views
645
Vbapadawan
V

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top