Worksheet matching to return row number
October 24, 2008 • JP • No Comments • Rate This Article
• Links to this article
I have a worksheet with a list of company names in column A (we'll call it 'Worksheet1'), and another worksheet with a list of similar company names in column A ('Worksheet2'). Both lists have a header row, so the data starts in cell A2 of each worksheet.
My boss wants me to check if any of the company names listed in Worksheet1 are also in Worksheet2. First I try a simple VLOOKUP formula in cell B2 of Worksheet1, filled down along column A down to the end of my data.
=VLOOKUP(A2,Worksheet2!<cell range>,1,FALSE)
But this produces the ugly "#N/A" error. So I wrap my formula in an IF statement, using the ISNA Function to trap the error and instead display a friendly error message.
=IF(ISNA(VLOOKUP(A2,Worksheet2!<cell range>,1,FALSE)),"Not Found","Found")
But a review of my Worksheet2 data reveals some of the cells have hidden spaces, misspellings, filler words like "The" and "Inc." and so on. So I decide to turn the formula into a moderately fuzzy match.
=IF(ISNA(VLOOKUP("*"&A2&"*",Worksheet2!<cell range>,1,FALSE)),"Not Found","Found")
Before I send it to my boss, I anticipate his request and decide instead of just printing a friendly error message, to make it easy to find the row where the match was found. So instead of "Found", I need to return the row number. I know the MATCH formula can do this.
="Match found in row #"&MATCH("*"&A2&"*",Worksheet2!<cell range>,0)+1
I integrate this into the formula above to get:
=IF(ISNA(VLOOKUP("*"&A2&"*",Worksheet2!<cell range>,1,FALSE)),"Not Found","Match found in row #"&MATCH("*"&A2&"*",Worksheet2!<cell range>,0)+1
Now my lookup formula will return "Not found" if the string in A2 is not found anywhere in the given range (even in the middle of a cell), otherwise it will return the row number where it was found.
Enjoy,
JP
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: OutlookCode.com under new management
Next Post: HTML Link Building Using Excel
nice works jp
thanks for coming by to my blog and correcting my mistake jp, and i just add your excel site to my blogroll as my compliment for you.
thanks again.
No problem Poer, glad to help. Thanks for the link!
–JP
JP,
nice tip.
A short amendment: You can use question marks in the lookup value as well. E.g. if the lookup value is "??A*" VLOOKUP will look for the first entry with an "A" as the third letter.
By the way: The trick works for HLOOKUP, MATCH, etc. as well.
Even more sophisticated, VBA-based fuzzy functions for lookups and matches are described here:
http://hairyears.livejournal.com/115867.html#VFuzzyLookup
Kind regards
Robert
Definitely. I like the "*" better because it matches any number of characters, but the "?" can also be used depending on your needs. I like that page, it is in the VBA search engine. There's also a post on mrexcel.com with some fuzzy match functions.
–JP
So, my problem is a bit different. What if I have blocks of data and I want Excel to return the row number when it finds a new number?
Example…
Industry Value (Header Row)
2300
2300
2300
2300
5200
5200
5200
So, I would want it to return the row number for the first 2300 and the first 5200, and so on.
Thanks for ANY help on this!!
Bob,
assumed you have your data in column A (A1 to A100) and you enter the value you are looking for in B1, you need this formula in cell C1:
=ROW(A1) -1 + MATCH(B1, A1:A100,0)
This returns the number of the row with the first match. If needed, you would have to add an error handling with an IF-clause for an input value that can't be found.
Great – thanks Robert! I will give that a try…