CODEWITHSUNDEEP
excel
steve park
steve park

Reputation: 41

IF(Match function returning #N/A when string is expected

I'm currently doing some data analysis work for reference data from two different sources.

For example from one system, A1 is Samoa, American and the other B1 is American Samoa.

I'm using the following function =IF(Match(A1,B1),"Match","Not")

For all the matching "Match" is coming up as expected. For all the "Not" matching cells, I am getting the #N/An error.

Can anyone explain to me how to fix this and why it is happening? Thanks!

Your post has been edited!!!

Upvotes: 0

Views: 279

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Match returns a number or an error not TRUE/FALSE.

The IF function assumes anything that is >0 to be TRUE. That is why when it matches it works.

Also when comparing two cells MATCH is overkill.

=IF(A1=B1,"Match","Not")

Match searches a range for a match and returns the relative position of that match.

So to search if A1 is in Column B, we would use:

=MATCH(A1,B:B,0)

If A1 is not found in column B the MATCH returns an error. If it is found it would return the relative position in the range, in this case it would equal the row number.

For more information on the MATCH function see: https://support.office.com/en-us/article/MATCH-function-e8dffd45-c762-47d6-bf89-533f4a37673a?ui=en-US&rs=en-US&ad=US&fromAR=1

Upvotes: 3

Related Questions