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

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
LastFirstOther PhoneWork PhoneHome Phone
MysterioRey7864866789
SteinerScott
9546329085

<tbody>
</tbody>
3972598
ColdStone
6612628976

<tbody>
</tbody>
78630585356
Lisa Ray
3619455588

<tbody>
</tbody>
2249332353

<tbody>
</tbody>

<tbody>
</tbody>

Call_Logs Sheet
Phone NumberDateTimeDurationName
3972598

<tbody>
</tbody>
Mon 05/11/2015

<tbody>
</tbody>
3:51 PM

<tbody>
</tbody>
0:02:51

<tbody>
</tbody>
6612628976

<tbody>
</tbody>
Fri 04/24/2015

<tbody>
</tbody>
4:27 PM

<tbody>
</tbody>
0:01:18

<tbody>
</tbody>
7864866789

<tbody>
</tbody>
Tue 03/31/2015

<tbody>
</tbody>
12:02 PM

<tbody>
</tbody>
0:03:53

<tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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?
 

cerebral

New Member
Joined
Jun 16, 2015
Messages
9
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
ABCDE
LastFirstOther PhoneWork PhoneHome Phone
MysterioRey7864866789
SteinerScott
9546329085

<tbody>
</tbody>

3972598
ColdStone
6612628976

<tbody>
</tbody>

78630585356
LisaRay
3619455588

<tbody>
</tbody>

2249332353

<tbody>
</tbody>


<tbody>
</tbody>


Call_Logs Sheet
ABCDEF
Phone NumberDateTimeDurationFirst NameLast Name
3972598

<tbody>
</tbody>

Mon 05/11/2015

<tbody>
</tbody>

3:51 PM

<tbody>
</tbody>

0:02:51

<tbody>
</tbody>

6612628976

<tbody>
</tbody>

Fri 04/24/2015

<tbody>
</tbody>

4:27 PM

<tbody>
</tbody>

0:01:18

<tbody>
</tbody>

7864866789

<tbody>
</tbody>

Tue 03/31/2015

<tbody>
</tbody>

12:02 PM

<tbody>
</tbody>

0:03:53

<tbody>
</tbody>


<tbody>
</tbody>
 

cerebral

New Member
Joined
Jun 16, 2015
Messages
9
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
ABCDE
LastFirstOther PhoneWork PhoneHome Phone
MysterioRey7864866789
SteinerScott
9546329085

<tbody>
</tbody>

3972598
ColdStone
6612628976

<tbody>
</tbody>

78630585356
LisaRay
3619455588

<tbody>
</tbody>

2249332353

<tbody>
</tbody>


<tbody>
</tbody>



Call_Logs Sheet
ABCDEF
Phone NumberDateTimeDurationFirst NameLast Name
3972598

<tbody>
</tbody>

Mon 05/11/2015

<tbody>
</tbody>

3:51 PM

<tbody>
</tbody>

0:02:51

<tbody>
</tbody>

6612628976

<tbody>
</tbody>

Fri 04/24/2015

<tbody>
</tbody>

4:27 PM

<tbody>
</tbody>

0:01:18

<tbody>
</tbody>

7864866789

<tbody>
</tbody>

Tue 03/31/2015

<tbody>
</tbody>

12:02 PM

<tbody>
</tbody>

0:03:53

<tbody>
</tbody>


<tbody>
</tbody>

 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

cerebral

New Member
Joined
Jun 16, 2015
Messages
9
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.

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.
 

cerebral

New Member
Joined
Jun 16, 2015
Messages
9

ADVERTISEMENT

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.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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).
 

cerebral

New Member
Joined
Jun 16, 2015
Messages
9
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).

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:

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top