Vlookup not working until i press in enter in cell

Hi Guys

Vlookup wont work until I click in the cell and press enter on the cells that I am looking up from.

Very weird, both are formatted in number.

Any suggestions?

You probably need to change settings.

2003: Tools > Options > Calculation > Calculation > Automatic.

2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.

2010 and 2013: File > Options > Formulas > Workbook Calculation > Automatic

Hi I noticed a green mark at top and for some reason it stated that the cells were in text format, so highlighted them all and changed to number, then formatted them again to number no decimal and it worked.

Sounds like they are 'Numbers Stored As Text'

Try copying a blank cell
Highlight the problematic cells (the ones you have to click into and press enter for it to work)
Right click - Paste Special - Values - Add - OK.

If your lookup table has codes which are all formatted as text values (including those that are pure numbers), then you can write your VLOOKUP formula as:

=VLOOKUP(A2&"", ...

which will convert the lookup_value in A2 into text.

Hope this helps.