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

[cerebral]

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

Last	First	Other Phone	Work Phone	Home Phone
Mysterio	Rey	7864866789
Steiner	Scott	9546329085 <tbody> </tbody>		3972598
Cold	Stone	6612628976 <tbody> </tbody>		78630585356
Lisa	Ray	3619455588 <tbody> </tbody>	2249332353 <tbody> </tbody>

<tbody>
</tbody>

Call_Logs Sheet

Phone Number	Date	Time	Duration	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]

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]

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

A	B	C	D	E
Last	First	Other Phone	Work Phone	Home Phone
Mysterio	Rey	7864866789
Steiner	Scott	9546329085 <tbody> </tbody>		3972598
Cold	Stone	6612628976 <tbody> </tbody>		78630585356
Lisa	Ray	3619455588 <tbody> </tbody>	2249332353 <tbody> </tbody>

<tbody>
</tbody>

Call_Logs Sheet

A	B	C	D	E	F
Phone Number	Date	Time	Duration	First Name	Last 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]

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

A	B	C	D	E
Last	First	Other Phone	Work Phone	Home Phone
Mysterio	Rey	7864866789
Steiner	Scott	9546329085 <tbody> </tbody>		3972598
Cold	Stone	6612628976 <tbody> </tbody>		78630585356
Lisa	Ray	3619455588 <tbody> </tbody>	2249332353 <tbody> </tbody>

<tbody>
</tbody>

Call_Logs Sheet

A	B	C	D	E	F
Phone Number	Date	Time	Duration	First Name	Last 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]

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]

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]

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]

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]

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

 

[svendiamond]

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