Vlookup formula not working

**RightPlace** · 11-07-2011, 10:06 AM

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...

**Domski** · 11-07-2011, 10:16 AM

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

**RightPlace** · 11-07-2011, 10:39 AM

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.

**Domski** · 11-07-2011, 10:54 AM

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

**arthurbr** · 11-07-2011, 11:03 AM

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

**RightPlace** · 11-07-2011, 11:22 AM

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'.