Match ID and fill down with data

[philmatth]

I need to fill down column E (Phone 2) with the phone number where the client ID in column A matches the Phone ID in column C

Column A has 184 records, column C and D have 13,022 records. Any help would be greatly appreciated

Client ID	Name	Phone ID	Phone	phone2
3​	Gregory	3​	717 203 0044	717 203 0044
29​	Therese	29​	845-677-8886	845-677-8886
269​	Cheryl	345​	7074791233	7074791233
345​	Edward	346​	3607946070
401​	Susanna	347​	706-291-9428
439​	Gilles	348​	6314231982
451​	Nancy	349​	604-615-5640
452​	Thomas	350​	253-638-2548
522​	Dr. Marji	351​	8286809606
530​	Leigh	352​	617-869-0862
531​	Laurie	353​	516 665-9678
876​	Pamela	354​	707-758-3319
1031​	jolie	355​	5104077806

 

[etaf]

=IF(COUNTIF($A$3:$A$15,C3),D3,"")

i have put formula in F - just to compare to your expected results

Book6
	A	B	C	D	E	F
2	Client ID	Name	Phone ID	Phone	phone2
3	3	Gregory	3	717 203 0044	717 203 0044	717 203 0044
4	29	Therese	29	845-677-8886	845-677-8886	845-677-8886
5	269	Cheryl	345	7074791233	7074791233	7074791233
6	345	Edward	346	3607946070
7	401	Susanna	347	706-291-9428
8	439	Gilles	348	6314231982
9	451	Nancy	349	604-615-5640
10	452	Thomas	350	253-638-2548
11	522	Dr. Marji	351	8286809606
12	530	Leigh	352	617-869-0862
13	531	Laurie	353	516 665-9678
14	876	Pamela	354	707-758-3319
15	1031	jolie	355	5104077806
Sheet1

Cell Formulas
Range	Formula
F3:F15	F3	=IF(COUNTIF($A$3:$A$15,C3),D3,"")

 

[philmatth]

=IF(COUNTIF($A$3:$A$15,C3),D3,"")

i have put formula in F - just to compare to your expected results

Book6
	A	B	C	D	E	F
2	Client ID	Name	Phone ID	Phone	phone2
3	3	Gregory	3	717 203 0044	717 203 0044	717 203 0044
4	29	Therese	29	845-677-8886	845-677-8886	845-677-8886
5	269	Cheryl	345	7074791233	7074791233	7074791233
6	345	Edward	346	3607946070
7	401	Susanna	347	706-291-9428
8	439	Gilles	348	6314231982
9	451	Nancy	349	604-615-5640
10	452	Thomas	350	253-638-2548
11	522	Dr. Marji	351	8286809606
12	530	Leigh	352	617-869-0862
13	531	Laurie	353	516 665-9678
14	876	Pamela	354	707-758-3319
15	1031	jolie	355	5104077806
Sheet1

Cell Formulas
Range	Formula
F3:F15	F3	=IF(COUNTIF($A$3:$A$15,C3),D3,"")

Hi Etaf,
sorry that's not working for me, The phone number 7074791233 is assigned to the phone ID 345 but the client ID is 269, the phone ID 269 is further down the sheet

 

[etaf]

your example needs to show expectred results

how about
=IFERROR(INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0)),"")

As only 3 IDS match i changed 1 further down 522 to match and get the number

Book1
	A	B	C	D	E
1	Client ID	Name	Phone ID	Phone	phone2
2	3	Gregory	3	717 203 0044	717 203 0044
3	29	Therese	29	845-677-8886	845-677-8886
4	269	Cheryl	345	7074791233
5	345	Edward	346	3607946070	7074791233
6	401	Susanna	347	706-291-9428
7	439	Gilles	348	6314231982
8	451	Nancy	349	604-615-5640
9	452	Thomas	350	253-638-2548
10	522	Dr. Marji	351	8286809606	707-758-3319
11	530	Leigh	352	617-869-0862
12	531	Laurie	353	516 665-9678
13	876	Pamela	522	707-758-3319
14	1031	jolie	355	5104077806
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IFERROR(INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0)),"")

 

[philmatth]

your example needs to show expectred results

how about
=IFERROR(INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0)),"")

As only 3 IDS match i changed 1 further down 522 to match and get the number


Thanks for the quick reply and sorry for the confudsion. I've entered that in E2 and I just get #NAME?

 

[etaf]

did you copy and paste the formula , or retype it ??? Name usually means a typing error, function NOT known, range not a real range typo

no need to copy my post into a reply

 

[philmatth]

I copied and pasted it

 

[etaf]

ok, it works for me

CONFIRM
what version of excel - you say 2003 or older - not sure when index/match was released - but i suspect earlier .....


it maybe IFERROR

try without the iferror

=INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0))

or this will work - but give n/a where no match

=VLOOKUP(A2,$C$2:$D$14,2,FALSE)

then we can think about the error

Book3
	A	B	C	D	E	F
1	Client ID	Name	Phone ID	Phone	phone2
2	3	Gregory	3	717 203 0044	717 203 0044	717 203 0044
3	29	Therese	29	845-677-8886	845-677-8886	845-677-8886
4	269	Cheryl	345	7074791233		#N/A
5	345	Edward	346	3607946070	7074791233	7074791233
6	401	Susanna	347	706-291-9428		#N/A
7	439	Gilles	348	6314231982		#N/A
8	451	Nancy	349	604-615-5640		#N/A
9	452	Thomas	350	253-638-2548		#N/A
10	522	Dr. Marji	351	8286809606	707-758-3319	707-758-3319
11	530	Leigh	352	617-869-0862		#N/A
12	531	Laurie	353	516 665-9678		#N/A
13	876	Pamela	522	707-758-3319		#N/A
14	1031	jolie	355	5104077806		#N/A
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IFERROR(INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0)),"")
F2:F14	F2	=VLOOKUP(A2,$C$2:$D$14,2,FALSE)

 

[philmatth]

Perfect, thanks so much, =VLOOKUP(A2,$C$2:$D$14,2,FALSE) worked for me. I really do appreciate the help here, once again thank you!

 

[etaf]

you are welcome

it must be the IFERROR issue , as added in version 2007

ISERROR is available in earlier versions
=IF(ISERROR(VLOOKUP(A2,$C$2:$D$14,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$14,2,FALSE))

Book3
	A	B	C	D	E	F
1	Client ID	Name	Phone ID	Phone	phone2
2	3	Gregory	3	717 203 0044	717 203 0044	717 203 0044
3	29	Therese	29	845-677-8886	845-677-8886	845-677-8886
4	269	Cheryl	345	7074791233
5	345	Edward	346	3607946070	7074791233	7074791233
6	401	Susanna	347	706-291-9428
7	439	Gilles	348	6314231982
8	451	Nancy	349	604-615-5640
9	452	Thomas	350	253-638-2548
10	522	Dr. Marji	351	8286809606	707-758-3319	707-758-3319
11	530	Leigh	352	617-869-0862
12	531	Laurie	353	516 665-9678
13	876	Pamela	522	707-758-3319
14	1031	jolie	355	5104077806
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IF(ISERROR(INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0))),"",INDEX($D$2:$D$14,MATCH(A2,$C$2:$C$14,0)))
F2:F14	F2	=IF(ISERROR(VLOOKUP(A2,$C$2:$D$14,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$14,2,FALSE))