Clean It Up...If You Want

You'll notice in column F under Clean Match it says No if it didn't find a match, and Yes with the position in brackets if it did find a match. To do this you can use the IF and ISERROR function along with MATCH to make it a little cleaner.

The formula in cell F3 looks like this:

**=IF(ISERROR(MATCH(D3,B$3:B$8,0)),"No","Yes (" & MATCH(D3,B$3:B$8,0) & ")")**. It's a little longer and more complicated but if we break it down you'll see what is happening.

The IF function has 3 parts: (1) What you want to check (2) What to do if it's True (3) What to do if it's False. In this case we're checking if ISERROR(MATCH(D3,B$3:B$8,0)) is True or False. We already know what the MATCH function does, and the ISERROR function just checks to see if the MATCH function returns an error (i.e. no match) or a number. If MATCH returns a #N/A then ISERROR will be True, otherwise it will be False.

Now, if ISERROR is True (i.e. we didn't find a match) then the IF function will return the value "No". If ISERROR is False (i.e. we found a match) then the IF function will return the value "Yes (" & MATCH(D3,B$3:B$8,0) & ")". This is just the text Yes ( and then we get the position of the lookup value using the MATCH function and close it off with more text, in this case " )", and this is why we got the value Yes(6) when looking for Lime in list 1.

It isn't necessary to do this but sometimes it looks a lot nicer to have a specific result when you find or don't find a match.