Web Tech Help
 Compare Lists in Excel - MATCH FunctionMATCH is a useful Excel function to compare listsFebruary 19, 2006Paul 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. Previous Page 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