Vlookup value not working properly in other formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1088082%22%20slang%3D%22en-US%22%3EVlookup%20value%20not%20working%20properly%20in%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1088082%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20%3Dvlookup()%20to%20obtain%20values%20from%20data%20that%20was%20imported%20from%20another%20workbook%20(AISC%20Shapes%20Database%20if%20that%20helps).%20I%20used%20%22Get%20Data%20%26gt%3B%20From%20File%20%26gt%3B%20From%20Workbook%22%20to%20get%20the%20data%20into%20my%20workbook.%20The%20imported%20data%20is%20in%20a%20separate%20sheet%2Ftab%20in%20my%20current%20workbook.%20All%20of%20the%20values%20that%20I%20am%20looking%20up%20are%20formatted%20as%20numbers.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20sheet%20that%20I%20am%20working%20in%2C%20the%20vlookup%20value%20returned%20is%20correct%2C%20so%20no%20problems%20there.%20When%20I%20try%20to%20use%20the%20vlookup'd%20value%20in%20another%20formula%2C%20that's%20when%20I%20run%20into%20problems.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EExample%3A%3CBR%20%2F%3EA1%20is%20the%20lookup%20value%3CBR%20%2F%3EA2%20is%20the%20%3Dvlookup()%20function%2C%20using%20A1%20as%20the%20lookup%20value%2C%20and%20the%20imported%20data%20as%20the%20table%20array.%20For%20this%20example%2C%20lets%20say%20the%20returned%20value%20ends%20up%20being%206%20and%20is%20formatted%20as%20a%20number.%3CBR%20%2F%3EA3%20is%20a%20input%20value.%20Lets%20just%20say%20it's%208%20and%20is%20formatted%20as%20a%20number.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20A4%2C%20if%20I%20use%20the%20formula%20%3DA3%26gt%3BA2%2C%20the%20returned%20value%20is%20FALSE%20though%208%20is%20clearly%20larger%20than%206.%20If%20I%20use%20the%20formula%20%3DA3%3CA2%3E%3C%2FA2%3E%3CBR%20%2F%3EI%20noticed%2C%20however%2C%20in%20A2%20if%20I%20add%20%2B0%20to%20the%20end%20of%20my%20formula%20(%20%3Dvlookup()%2B0%20)%2C%20the%20returned%20value%20in%20A4%20becomes%20correct.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20idea%20what's%20going%20on%20here%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1088082%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1088138%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20value%20not%20working%20properly%20in%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1088138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F505778%22%20target%3D%22_blank%22%3E%40mbancroft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20of%20your%20numbers%20is%20actually%20text.%20Use%20ISTEXT()%20to%20check.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1088173%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20value%20not%20working%20properly%20in%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1088173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F505778%22%20target%3D%22_blank%22%3E%40mbancroft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%20in%20addition%2C%20any%20text%20is%20always%20more%20than%20any%20number%2C%20i.e.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%226%22%20%26gt%3B%208%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ereturns%20TRUE.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1092394%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20value%20not%20working%20properly%20in%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1092394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3Bit%20appears%20that%20the%20value%20is%20being%20treated%20as%20text%2C%20even%20though%20the%20source%20data%2C%20the%20imported%20data%2C%20and%20the%20vlookup%20cell%20are%20all%20formatted%20as%20numbers.%20I%20have%20copied%20in%20the%20data%20instead%20of%20importing%20it%20and%20that%20did%20the%20trick%20for%20now.%20Thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am using =vlookup() to obtain values from data that was imported from another workbook (AISC Shapes Database if that helps). I used "Get Data > From File > From Workbook" to get the data into my workbook. The imported data is in a separate sheet/tab in my current workbook. All of the values that I am looking up are formatted as numbers. 

In the sheet that I am working in, the vlookup value returned is correct, so no problems there. When I try to use the vlookup'd value in another formula, that's when I run into problems. 

Example:
A1 is the lookup value
A2 is the =vlookup() function, using A1 as the lookup value, and the imported data as the table array. For this example, lets say the returned value ends up being 6 and is formatted as a number.
A3 is a input value. Lets just say it's 8 and is formatted as a number.

In A4, if I use the formula =A3>A2, the returned value is FALSE though 8 is clearly larger than 6. If I use the formula =A3<A2, the returned value is TRUE though 8 is clearly greater than 6. 

I noticed, however, in A2 if I add +0 to the end of my formula ( =vlookup()+0 ), the returned value in A4 becomes correct. 

Any idea what's going on here?

3 Replies
Highlighted

@mbancroft 

On of your numbers is actually text. Use ISTEXT() to check.

 

Highlighted

@mbancroft 

As a comment in addition, any text is always more than any number, i.e.

="6" > 8

returns TRUE.

Highlighted

@Detlef Lewin it appears that the value is being treated as text, even though the source data, the imported data, and the vlookup cell are all formatted as numbers. I have copied in the data instead of importing it and that did the trick for now. Thanks for your help!