Vlookup inconsistencies and remedy

[Tigerexcel]

A 2 part question. Everyone has had the issue of Vlookup returning an error when a field is formatted as text and it requires a number. I run into this issue but it doesn't always return an error, sometimes even on the same files it returns inconsistent results. I notice it particularly when I download an external file. What is the main factor in causing this error, is it that the lookup value is formatted as text, or is the table array the major issue? The lookup formula will work sometimes when one or the other is formatted as General or Number but not always.
Even when you have formatted both the lookup value and the corresponding entry in the table array, a re-set is sometimes required to return the correct value. I usually do this by re-typing some of the lookup formula and that does the trick. I'd like to combine all these steps into a one-step process which will probably a macro so that I speed up the processing, anyone have such a macro?

 

[jasonb75]

Retyping a formula in whole or part wouldn't fix any such issues, best guess would be that you have set calculation to manual.

Without seeing a sample with incorrect and expected results it is impossible to diagnose the exact cause.

 

[Tigerexcel]

Calculation set to automatic, sometimes the lookup formula seems to need a re-set for the change from text to general/value to take effect.

 

[jasonb75]

Formulas don't need to be 'reset', that is merely an indication of another unidentified problem.

 

[Tigerexcel]

I'm quoting the Microsoft site: Solutions to Three Common Problems when Using VLOOKUP() - Microsoft 365 Blog

The first solution that will normally occur to us is “I’ll just format the General column as Text” (or vice-versa). So we highlight one of the columns and hit Ctrl+F1 (or Home | Format | Format Cells (2007, 2010) or Format | Cells… (2003 & below)) and change the format and …. What!? It doesn’t fix the problem. Changing a cell’s format doesn’t “take” until you edit the cell. If we have more than just a few rows, we are not going to want to plink back and forth hitting F2 then ENTER a couple hundred times. We have two other options. One is to use Excel’s built-in error correct if it has flagged this for us. In the screenshot below it has done so. We can then highlight all of the cells in that column and pick “Convert to Number” from the error correction popup menu.

 

[jasonb75]

Incorrect information leads to incorrect answers, with most things excel the smallest nuance makes the biggest difference.

I usually do this by re-typing some of the lookup formula and that does the trick.

The problem that you're quoting refers to re-typing the source data not the formula, so which of those are you actually doing?

 

[MARK858]

What is the main factor in causing this error, is it that the lookup value is formatted as text

I think you are confusing formatting of a cell with the value of a cell.
Changing the format only affects the appearance of the cell and not the value. If the value is imported as text then changing the cells format to General or Number doesn't change the underlying value.
VLOOKUP looks at the cell value.
You are better off trying to convert the Lookup value using Text to Columns.

 

[Tigerexcel]

Incorrect information leads to incorrect answers, with most things excel the smallest nuance makes the biggest difference.

The problem that you're quoting refers to re-typing the source data not the formula, so which of those are you actually doing?

There is no change to the source data, it's an Excel quirk. Simply re-formatting cells isn't always sufficient for the formula to work, just re-typing the = sign or another part of the lookup formula is needed. There is no change to the actual formula.

 

[Tigerexcel]

I think you are confusing formatting of a cell with the value of a cell.
Changing the format only affects the appearance of the cell and not the value. If the value is imported as text then changing the cells format to General or Number doesn't change the underlying value.
VLOOKUP looks at the cell value.
You are better off trying to convert the Lookup value using Text to Columns.

Ok, and sometimes I use Text to Columns to do this, however a reset is still needed for the formula to work.

 

[MARK858]

Can you upload to a free file sharing site like www.box.com or www.dropbox.com an example of a workbook where you would need to do a "reset" after using Text to Columns please (including the formulas that you are using).