Can a MATCH formula return the matching string instead of a boolean value?

Edit: [Sample Workbook in post #4 and Resolved in post # 5]

Hi Guys,

I am using this formula in Sheet1 to let me know if there are matches in a range in sheet2
=ISNA(MATCH(A1,Sheet2!$A$1:$A$110,0))
It returns False if there is a match and True if there is now match.

Is it possible to have a formula that will return the string value of the matching value instead of True/False?

Thanks :)

Yes, you can either combine it with INDEX() or you can use VLOOKUP().

Before suggesting a complete formula, which version of XL are you using?

Hi, hopefully I'm understanding your question right.

If you're wanting to return the matching string and not True/False or the Row/Col then you could use this formula:
=IF(ISNA(LOOKUP(A1,Sheet2!A1:A110))=TRUE,"No Match Found",LOOKUP(A1,Sheet2!A1:A110))

This will show the matching text or if no match found, display "No Match Found". Hope this is what you're looking for.

Hi Colin Legg

Please look at my attached workbook to see what I am attempting.

I prefer having it work in both 2003 and 2007, but If there is a formula that works in 2007 but not 2003, then the 2007 formula is what I really need. (For 2003 I'm curious what I could use for that, but it's not important)

Thanks a lot I really appreciate any help.

----------------------------------------
Thanks TheRealTinTin

I think its too late for me, as I may be missing something. It is returning some matches, and some existing strings in the wrong cell and it never reports No Match Found for strings that I know do not exist in Sheet2. Please look at the attached workbook and let me know what I did wrong
=IF(ISNA(LOOKUP(A1,Sheet2!$A$1:$A$10027))=TRUE,"No Match Found",LOOKUP(A1,Sheet2!$A$1:$A$10027))

I used an absolute search range in [Sheet2] because in [Sheet1] I'm dragging this formula down from C1 to C9916
The strings in Column A in [Sheet1] I am using this formula in Column C to see which of the same strings exist in Column A of [Sheet2]. Most do exist.
A few I know do not exist, so I highlighted those cells to make it easier to test the formula.

Thanks

Hi,

LOOKUP() isn't quite the right function here because of this remark in the helpfile:

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

So it will return an approximate match, which isn't what you want in this situation.


Since you only want to return the look up value when a match is found, you could just use this (which is fine for 2003 too):

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),A1,"No Match Found")


Hope that helps...

[Resolved in post # 5]

Thanks Colin Legg

That has worked like a charm with everything I've thrown at it so far.

I am though confused by the ISNUMBER part of the formula, as one of the sample values I tried is the text string "Shop Jacket With Out Logo"

The formula handles that perfectly regardless of whether I have the cell formatted as text, general or number.

For the sake of expanding my knowledge could you explain a little about what ISNUMBER is doing with my text string example above??

I noticed that not using ISNUMBER causes the "No Match Found" part of the formula not to work, it instead return's #N/A, but other than that seems to work fine.

Hi,


For the sake of expanding my knowledge could you explain a little about what ISNUMBER is doing with my text string example above??

If MATCH() returns a number then ISNUMBER() returns TRUE. If MATCH() returns anything else (ie. An error) then ISNUMBER() returns FALSE. So effectively we're converting data types into something useful for the IF() function.

MATCH() returns number ---> ISNUMBER() returns TRUE ---> IF() returns the value in A1
MATCH() returns error ---> ISNUMBER() returns FALSE ---> IF() returns "No Match Found"


I noticed that not using ISNUMBER causes the "No Match Found" part of the formula not to work, it instead return's #N/A, but other than that seems to work fine.

We have to wrap MATCH() with a function such as ISNUMBER() because if it directly returns an error to IF() then IF() will also return an error (which you don't want here).

Thanks,

I'm probably being thick headed, but I still don't understand why ISNUMBER returns TRUE ---> IF() returns the value in A1
when A1 is a string not a number

The string I used to test it in column A is: "Shop Jacket With Out Logo"

"Shop Jacket With Out Logo" is in Sheet2 Column A so the formula results in the match value "Shop Jacket With Out Logo" as I want.
I just do not understand why ISNUMBER in this case works for both numbers and strings,

Hi,

No problem - ask away because this is important to understand. :)

You are passing a string (the value in A1) into MATCH(), but the output of the MATCH() function is a number (or an error).

Think of it as MATCH() asking the question, "Where is this string in these cells?". You tell it the string you are looking for and it tells you the position of that string if it finds a match.



So, first let's look at this part of the formula:

MATCH("Shop Jacket With Out Logo",Sheet2!A:A,0)

This returns the position of the string in the range (if a match is found) - this is a number value. If not found then it returns #N/A, which is an error value.


ISNUMBER() then converts either a number (which represents when a match was found) to TRUE or converts #N/A (when no match found) to FALSE. We can do this because we're not interested in knowing exactly where the match was found, we just need to know whether or not the match was found. Think of it as ISNUMBER() asking the question, "Did we find a match or not?".



The IF() function then returns the result we specify depending on whether the condition is TRUE or FALSE. The syntax for IF() is like this:

IF ( Logical Test , [Value If True] , [Value If False] )


Our simplified formula looks like:

IF ( ISNUMBER(.....) , A1 , "No Match Found" )


So we're saying that if ISNUMBER() returns TRUE then return the value in A1, otherwise return "No Match Found". The caveat is that if the logical test is an error, then IF() returns an error too. That's why we use ISNUMBER() to convert #N/A error to FALSE. Think of it as IF() asking the question, "What to do if a match was found or not?".



Where is this string in these cells? Did we find a match? What to do if a match was found or not?

Found: MATCH() returns number ---> ISNUMBER() returns TRUE ---> IF() returns the value in A1
Not Found: MATCH() returns error ---> ISNUMBER() returns FALSE ---> IF() returns "No Match Found"

Thanks so very much!

Your explanations are what I needed to be able get my head wrapped around that fully. - Understanding it well is great for expanding my knowledge, but also just plain feels good.

I really appreciate you being willing and thoughtful enough to take the time to explain it so completely. I really have learned a lot from it.

Have a great day :)
- - - - - - - - - - - - - - - - - - - - -- -

Edit:

While I'm here, I added wild cards to the formula like this:
=IF(ISNUMBER(MATCH("*"&A1&"*",Sheet2!A:A,0)),A1,"No Match Found")
- It seems to work fine to find if there is a match that contains the value in A1
Is there any fault you can tell me in doing that?

While I'm here, I added wild cards to the formula like this:

That's fine. Just be aware that if there are multiple matches, only the position of the first match will be returned.

Perfect.

Thanks again for spelling out all the elements involved so thoroughly, so the average mind can easily understand what's going on.