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

[Tyron]

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:

=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

 

[JoeMo]

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]

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]

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:

=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]

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]

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.

=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]

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.

=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.