Extra spaces before/after the data? Vlookup is very particular, whereas search will find the item even with an extra space

Check cell data types match, try ordering the data alphabetically.

#na says teh forumla is working. IS teh data in the cells the correct format. ie number not text etc?? I sometimes need to put a ' in front to make excel register the number, concatenate if i need to work with a large data set

Does the formula work if you add a line for a made up supplier of "TEST" and try to vlookup for that?

sometimes need to put a ‘ in front to make excel register the number,

The OP is using supplier name which should be text, unless all of the suppliers happen to have names that are 100% numeric.

Does MATCH() find the data?

I'm not an Excel guru by any stretch, but I've had a similar problem in the past. Try double clicking in the cell and selecting (highlighting) the value you want to find, then ctrl+c to copy it, then paste it into the find box. This has worked for me in the past, when simply typing the value in wouldn't.

@bails yeah I'm still to have my morning coffee 🙂

Has the OP also just simply done a find to make sure the supplier names are the same?

so either the formula isn't typed properly or there is some anomaly with the text. eg some variation of lower case and upper case or spaces?

Imported data might not be using characters you expect. just because one dash looks like another dash doesn't mean they're the same actual character. If what @forked suggests works then that's your problem.

Eg, type " " into Word and you'll get “ ” - these are different characters (and why a word processor does not make for a good programming environment). You'll likely hit this problem with names like "Honest Dan's Used Widgets" - there's several different characters that all look a lot like an apostrophe but only one of them is ASCII 39.

Oh, also, I don't know if Excel is this fussy but,

Try selecting the imported data then just pressing enter. Does it then work?

"Enter" on a Linux system and a Windows system are two different things. On Windows it's two characters, carriage return and line feed, but on Linux it's just a line feed. So if your source system is Linux or variants thereof then all your data is potentially missing the carriage return character and VLOOKUP et al might well pout about that.

I don't know enough about Excel's data handling to say with any confidence whether this is a non-issue or not, just chucking it out there for consideration.

+1

the usual culprits are rogue spaces before/after the text string (supplier name)

or two apostrophes faking it as speech marks

or zeros mOOnlighting as the letter O

the really insidious bugs are:

double spaces pretending to be a single space by being a much smaller font

lower case letter L replaced by the number 1

certain letters from a foreign language

The answer is initially to sort them and investigate. Then you can run an Advanced Filter on Unique Entries and spot any weird duplicates.