Vlookup, where number in the table is text or has leading zeros

[JV0710]

Good Day

Please can I get help with the following query

From a previous thread that was solved by Peter_SSs I am trying to lookup a number and return a name (taking into account that some of the numbers may be formatted as text).
The extra complication that I have is that some of the numbers can have leading Zero's.

In the pic attached : I am showing that I can find the "Supplier Number Name" where in column B the numbers have no leading zero's but I am unable to work out how to manage where there are leading zero's in column B

Hope this makes sense

Thanks very much in advance

Past thread reference:

How to force xlookup value even if the lookup value and lookup array is different format (one is text & one is number)

My lookup_value is in number format while my lookup_array is in text format, how can i avoid returning #N/A even if either lookup value and lookup array is in different format but the same number ABCD1Employee...

www.mrexcel.com

View attachment 75056

 

[Fluff]

Duplicate to: Vlookup number where number in the

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.