I came to this board a while back and someone gave me a neat vlookup formula which worked a treat, so I'm hoping someone will be as generous a second time round
I have the following formula in a different excel file this time, with the following syntax
=IF(ISNUMBER(MATCH(B2,'Suppliers'!A:A,0)),"Yes","No")
Given that:
In the workbook ('Matches') where I enter the formula, column B has a list of names. Workbook 'Suppliers', in column A, also has a list of names. I want Excel to tell me which of the names in column B 'Matches' it finds in column A 'Suppliers'.
It has found some, but for others it returns a 'No' even though the name IS there in both workbooks, and is an exact match. As it should be a 'Yes' I'm thinking something's wrong. I have enabled automatic calculation. Can anyone think of a reason?
Thanks in advance...
The usual problem is trailing spaces but it would help to see an example of where some names aren't matching. Can you load a sample workbook?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
I like to upload a sample workbook but as this is for work, all the information in the file is confidential to my employer so I cannot share it.
What I can tell you is that one supplier in particular is a single name without spaces and no special characters. I retyped the name in both workbooks and it changed to a 'Yes'. Could that be trailing spaces or formatting problems?
Also, do you know how to include partial matches in the search? Your help is appreciated.
Could be. Try using the CLEAN and TRIM functions on your lists. It should probably sort out the problem.
Depends what you mean by partial matches. Can you provide an example?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Please post in the right forum next time, this is an Outlook forum.
I will move this thread for you. Expect it to be closed the next time.
PS : indicating " somewhere" as location will not help once you have problems with regional settings
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
Apologies, it was an accidental posting in the wrong section
I've updated my location.
This is an example of a partial match I would like the formula to find:
Accenture / Accenture (UK) Limited
At the moment it returns a 'No' but I would like it to be a 'Yes'.
You might want to have a look at this:
http://www.microsoft.com/bi/en-us/Co...nforExcel.aspx
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks