Vlookup Formula will not return result

[Lauren Thomas]

Hello
I am really hoping that you can help me. I would be so grateful!

I am trying to generate a formula in column Q that will look up the data in column P which contains either a merchant ID number (long number) or a shop ID (short number) and generate the shop name. The vlookup table is on the tab 'Lookup' I am using Vlookup but it doesn't work for the rows that contain only the 4 digit number. I think that this could be to do with the fact that the numbers in column O which feed into P are generated by another formula but this doesn't make much sense because it works for the longer codes and the principal is the same.

Can you help?

Many thanks
Lauren

 

[pecoflyer]

Your "short numbers" in col P are text strings, while the lookup range "short numbers" are real numbers. The easiest way would be to change the short numbers on the "LOOKUP" sheet to text.
To do this, select the numbers - Data - Text to columns - Next - Next - Select " Text" in column data format - Finish
Custom aligning the columns has hidden this fact, text always aligns left, numbers, right.
Also using a function name as a tab name is not a good idea

 

[Lauren Thomas]

Hello
Thank you so much for your help, I have wasted so much time trying to work this out for myself! I am very grateful for your help

 

[shrivallabha]

See if following alternative works for you:
=LOOKUP(2,SEARCH(P2,LOOKUP!$A$1:$A$87,1),LOOKUP!$B$1:$B$87)