Use ISNA and MATCH functions to return a Yes or No when a match exists or not.

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey guys,

I have been trying to figure out why my Excel formula is not working, but can't seem to figure it out.
The web has a lot of information regarding ISNA, but when I use it the formula seems to be broken or something.
I have the following formula in D4 of Sheet1:
Code:
=IF(ISNA(MATCH(D3, Callers1, 0)), "No", "Yes")
For some reason whether the phone number matches or not it still shows the answer "No" for both cases.

Callers1 is a named range for Column A2:A100 on Sheet2 which only contains phone numbers.

D3 represents the cell used to input the telephone number
on Sheet1 (ex: 813-744-4309 [spam number by the way. lol]).

My goal is to have it check for a phone number. If it is there then it says yes in D4. If it doesn't find the phone number the it says No in D4.

Thanks in advance

Ty
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,235
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Are the phone numbers in Callers1 and D3 manually entered or imported? If you enter in an empty cell: =LEN(D3) what do you get? If you do the same for a number in Callers1 (say A2 on sheet2) what do you get?
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Are the phone numbers in Callers1 and D3 manually entered or imported? If you enter in an empty cell: =LEN(D3) what do you get? If you do the same for a number in Callers1 (say A2 on sheet2) what do you get?

Hey JoeMo,

Thanks for your reply. The phone number in sheet 1 is always manually entered.
While using the LEN function on Sheet1 D3 and Sheet2 A2 I get 12 for both.

Currently I have only one phone number in A2, but plan to add additional phone numbers once I am able to get the formula working. Have you been able to duplicate the conditions?

later

Ty
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey guys,

I have been trying to figure out why my Excel formula is not working, but can't seem to figure it out.
The web has a lot of information regarding ISNA, but when I use it the formula seems to be broken or something.
I have the following formula in D4 of Sheet1:
Code:
=IF(ISNA(MATCH(D3, Callers1, 0)), "No", "Yes")
For some reason whether the phone number matches or not it still shows the answer "No" for both cases.

Callers1 is a named range for Column A2:A100 on Sheet2 which only contains phone numbers.

D3 represents the cell used to input the telephone number
on Sheet1 (ex: 813-744-4309 [spam number by the way. lol]).

My goal is to have it check for a phone number. If it is there then it says yes in D4. If it doesn't find the phone number the it says No in D4.

Thanks in advance

Ty

Hey Everyone,

Looks like I discovered the problem. It appears that since my named range: Callers1 actually encompassed A1:E100 instead of A1:A100 it was unable to match correctly.

Has anyone else had this problem as while I was searching the web I did not encounter any mention that my above formula would not work as it encompasses more than one Column or Row?

later

Ty
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

Hey Everyone,

Looks like I discovered the problem. It appears that since my named range: Callers1 actually encompassed A1:E100 instead of A1:A100 it was unable to match correctly.

Has anyone else had this problem as while I was searching the web I did not encounter any mention that my above formula would not work as it encompasses more than one Column or Row?

later

Ty

MATCH is applicable only to a one dimensional reference. If you want to keep the Callers1 definition as is, try rather:

=IF(ISNA(MATCH(D3, INDEX(Callers1,0,1) 0)), "No", "Yes")

which takes the first column of Callers1 as target, i.e., A1:A100 of A1:E100.
 

Tyron

Active Member
Joined
Dec 5, 2012
Messages
258
Hey Aladin Akyurek,

Thank you for the clarification. I also appreciate the fact that you indicated a solution which allows me to still use the same named range by adding the INDEX function.

Code:
=IF(ISNA(MATCH(D3, INDEX(Callers1, 0, 1) 0)), "No", "Yes")

The above was my final formula and it works great. Thanks to everyone who helped.

later

Ty
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hey Aladin Akyurek,

Thank you for the clarification. I also appreciate the fact that you indicated a solution which allows me to still use the same named range by adding the INDEX function.

Code:
=IF(ISNA(MATCH(D3, INDEX(Callers1, 0, 1) 0)), "No", "Yes")

The above was my final formula and it works great. Thanks to everyone who helped.

later

Ty

You are welcome. Thanks for providing feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,871
Members
409,842
Latest member
mfernandezcean
Top