Vlookup number where number in the

[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

 

[Peter_SSs]

Try

=XLOOKUP(I7,B6:B18+0,C6:C18)

 

[JV0710]

Apologies for posting a duplicate thread. It was not done intentionally. I was trying to edit the thread Title and it somehow posted as a new thread. I did not realise that it was a duplicate until I posted it

 

[Chewyhairball]

Hi

maybe putting your formula inside

abs(vlookup(trim(I7)&"",trim(B6:C18)&"",2,0))

will help?

 

[JV0710]

Thanks very much Peter . . . your xlookup solution works great. For interest and my learning - Is there a way to solve if the user does not have office 365 and cannot use Xlookup

 

[Peter_SSs]

Is there a way to solve if the user does not have office 365 and cannot use Xlookup

Yes

=INDEX(C6:C18,MATCH(I7,B6:B18+0,0))

 

[JV0710]

Yes

=INDEX(C6:C18,MATCH(I7,B6:B18+0,0))

Super . . . Thanks very much for your help

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.