IF Logic HELP

[Walker_Ice]

Hi everyone,

I have a list of clients called "Client_Clientele". Another list named "Client_Phone" which holds all of their phone numbers associated with those clients. Another list named "Client_Email" which holds all of their emails as well.

I am trying to make a formula work, so that when I start typing a name that is already on the list, it automatically populates the phone numbers and emails. The problem is that when I type in a new name that is not on the list, it gives me the error "#REF". But I want it to stay empty if the name is not on the list "Client_Clientele". Can someone help me modify these formulas to work as desired?
Thank you in advance.

TEsting_Excel_File.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Client Name	Phone #	Email								Client Name	Phone #	Email
2	XX	687-000-0909	XX@gmail.com								XX	687-000-0909	XX@gmail.com
3	YY	687-000-0910	YY@gmail.com								YY	687-000-0910	YY@gmail.com
4	LIME	687-000-0911	LIME@gmail.com								LIME	687-000-0911	LIME@gmail.com
5	Yellow	687-000-0912	Yellow@gmail.com								Yellow	687-000-0912	Yellow@gmail.com
6	Green	687-000-0913	Green@gmail.com								Green	687-000-0913	Green@gmail.com
7	Blue	687-000-0914	Blue@gmail.com								Blue	687-000-0914	Blue@gmail.com
8	Blue		#NAME?
9	Yellow		#NAME?
10	Green		#NAME?
11
12
13
Sheet7

Cell Formulas
Range	Formula
B8:B10	B8	=IF(IFERROR(MATCH(A8, Client_clientele, 0), "")="", "", INDEX(Client_Phone, MATCH(A8, Client_clientele, 0), 1))
C8:C10	C8	=IF(A8<>"", INDEX(Client_Email, MATCH(A8, Client_clientele, 0)), "")

 

[SanjayGMusafir]

I think with Excel 2021 in your hand you should use XLOOKUP which automatically gives option what to return if not found.

Try that.

 

[Walker_Ice]

Hi Sanjay thank you for your reply,

If i use XLOOKUP, I still get the same error. I tried the following and still received an error when the name was not on the Client_Clientele list. They work great if in cell A8 I am typing in a name that is already existing on the "Client_Clientele list. The problem is when I have a new client, it gives me "#REF" error and I need it to just stay empty.

=XLOOKUP(A8, Client_clientele, Client_Phone, "", 0)
=IF(A8<>"", XLOOKUP(A8, Client_clientele, Client_Email, "", 0), "")

 

[SanjayGMusafir]

=XLOOKUP(A8, Client_clientele, Client_Phone, "", 0)

If the above formula generates right results when Client name in Column A exists

then you can use

=IFERROR(XLOOKUP(A8, Client_clientele, Client_Phone, "", 0),"")

 

[Walker_Ice]

If the above formula generates right results when Client name in Column A exists

then you can use

=IFERROR(XLOOKUP(A8, Client_clientele, Client_Phone, "", 0),"")

That worked perfectly! Thank you for helping solve this minor problem in a timely matter.

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Walker_Ice]

I'm sorry Sanjay,

After doing some further testing, I still continue to get the same error.

 

[SanjayGMusafir]

After doing some further testing, I still continue to get the same error.

No Problem

Share the XL2BB of actual data to understand the issue...

Also, need to understand the three lists you mentioned - Are the part of same table or how do they exist...