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:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,916
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,210

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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,584
Members
430,557
Latest member
MK15

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