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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,347
Messages
6,136,045
Members
449,981
Latest member
kjd513

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
Back
Top