Tech Help - providing the average home user with valuable information and tips on topics such as computers, the internet, and home electronics
Compare Lists in Excel - MATCH Function
MATCH is a useful Excel function to compare lists
February 19, 2006
Paul Jacobs
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.

Page: 2 of 2
You can submit your own article and have it published on Tech Help. Click on the link above for more details