Formulas & formatting

[drluke]

Is there a way to make excel ignore formatting of data cells so that my formula will work.
The data is typed into the worksheet, however the format is General. My formula won't work unless the typed in value is preceded by apostrophe. Is there a way to solve this problem?

My formula is:

=IF(VLOOKUP(A33,GTN!A:C,3,0)="5522","50019",(VLOOKUP(A33,GTN!A:C,3,0)))

 

[steve the fish]

Yeah dont use "5522" use instead 5522.

 

[drluke]

If I remove the inverted commas then I get 5522 as my result for those cells. That is not correct - the cells that have 5522 in them must be returned as 50015 in my formula result.

 

[steve the fish]

The only way you can get 5522 for the result of the formula:

=IF(VLOOKUP(A33,GTN!A:C,3,0)=5522,"50019",(VLOOKUP(A33,GTN!A:C,3,0)))

is if your vlookup produced 5522 in a text form. If your result was just a number 5522 then you would get the result 50019 in text form.