VLOOKUP stopped working when auto number hit 101.

J.Barrow

Help can anyone help me understand why my VLOOKUP formula stopped working and all projects lower than 101 have stopped working properly?

101 working properly.

098 not working properly.

Any answers would be great!

Andrée Starå

Hi @J.Barrow

I hope you're well and safe!

It's probably because 0** would be read as text, and 1** would be read as numbers, so you would need to have it consistent or add, for example, the VALUE function to convert it to a value regardless.

Make sense?

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

J.Barrow

@Andrée Starå

I am not following the need to make them Numbers as I am using the # Auto number column format? so why would it be reading as text vs a number?

Leibel S

@J.Barrow

Check the range (make sure it encompasses the entire columns).

also, add false to the VLOOKUP function at the end

See if that fixes

J.Barrow

@Leibel S

Thanks adding False to the end did make one cell work, but I am frustrated if that is the only solution or fix as I will need to add false to 714 formulas!!! as I have seven formulas per sheet that don't work and 102 sheets including the template. 😭

I am still not understanding why it worked, then did not at the 101.