Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text causes problem with VLOOKUP
I've received help here with a VLOOKUP question and it's worked great for me
so far... Thanks Max! New question. In the current VLOOKUP that I'm doing, one of my source files has the little green corners on the cells that tell you that numbers are stored as text. In the second source file, the cells are formatted to show as numbers stored as text, but the green corners are not there. I'm getting a lot of "no matches found" and I discovered that if I go to the second source file, double click on the cell like I am going to edit it, hit enter and move on, the corner is there all of a sudden and the VLOOKUP formula works. Is there any way to make those show up without clicking on each and every one of them? Thanks again in advance to anyone that might be able to help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text causes problem with VLOOKUP
Just to supplement a little to Bruce's response ..
If the VLOOKUP's table_array is in text, we could leave the table_array alone and convert the lookup values (presumably numbers keyed in manually) into text so that it matches, viz.: Instead of using either: =IF(A2="","",VLOOKUP(A2,New!A:B,2,0)) or =IF(A2="","",IF(ISNA(MATCH(A2,New!A:A,0)), "Part# not found in New",VLOOKUP(A2,New!A:B,2,0))) we could use something like: =IF(A2="","",VLOOKUP(A2&"",New!A:B,2,0)) or =IF(A2="","",IF(ISNA(MATCH(A2&"",New!A:A,0)), "Part# not found in New",VLOOKUP(A2&"",New!A:B,2,0))) (Lookup part number in A2 converted to text via joining it with a "") Alternatively, if the Part# in the table_array always contains leading zeros for 5 digits (say), i.e. a format of: 00000, then we could try something like: =IF(A2="","",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0) ) or =IF(A2="","",IF(ISNA(MATCH(TEXT(A2,"00000"),New!A: A,0)), "Part# not found in New",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers stored as text causes problem with VLOOKUP
Just to supplement a little to Bruce's response ..
If the VLOOKUP's table_array is in text, we could leave the table_array alone and convert the lookup values (presumably numbers keyed in manually) into text so that it matches, viz.: Instead of using either: =IF(A2="","",VLOOKUP(A2,New!A:B,2,0)) or =IF(A2="","",IF(ISNA(MATCH(A2,New!A:A,0)), "Part# not found in New",VLOOKUP(A2,New!A:B,2,0))) we could use something like: =IF(A2="","",VLOOKUP(A2&"",New!A:B,2,0)) or =IF(A2="","",IF(ISNA(MATCH(A2&"",New!A:A,0)), "Part# not found in New",VLOOKUP(A2&"",New!A:B,2,0))) (Lookup part number in A2 converted to text via joining it with a "") Alternatively, if the Part# in the table_array always contains leading zeros for 5 digits (say), i.e. a format of: 00000, then we could try something like: =IF(A2="","",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0) ) or =IF(A2="","",IF(ISNA(MATCH(TEXT(A2,"00000"),New!A: A,0)), "Part# not found in New",VLOOKUP(TEXT(A2,"00000"),New!A:B,2,0))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text and Date for VLOOKUP | Excel Worksheet Functions | |||
vlookup problem, possibly due to "noise" | Excel Worksheet Functions | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to reformat numbers stored as text (apostrophe at beginning) | Excel Discussion (Misc queries) | |||
VLOOKUP problem | Excel Discussion (Misc queries) |