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

#### Tyron

##### Active Member
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.

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?

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

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.

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

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.

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

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.

Replies
1
Views
77
Replies
8
Views
634
Replies
2
Views
550
Replies
4
Views
828
Replies
6
Views
3K

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.

### Which adblocker are you using?

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

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