Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup referencing one column that contains part numbers in both.
I'm seeing LOTS of posts that are close to this issue, but nothing quite the
same. I'm using VLOOKUP to look at a part number column that's being imported. It all comes across in text format b/c some part numbers are alphanumeric (ex: 6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the issue b/c if I change the entire column to text I get #N/A back for cells in the column that should show a number in them, (ex:30). If I change the entire column to numeric format I get the same for cells that contain alphanumeric data (ex: 6H). Does anyone know of a solution that might address, seeing how there are two different types of data formats in the same column being referenced?!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup referencing one column that contains part numbers in both.
Try something like this:
For ParNumbers in Col_A and a lookup table in Columns C:D Set all of the values in the Col_C of the lookup table Text. Then use a formula like this: B1: =VLOOKUP(A1&"",B:C,2,0) Appending an empty string to the numeric P/N's converts them to text. Appending an empty string to the text P/N's has no impact. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "crafty_girl" wrote: I'm seeing LOTS of posts that are close to this issue, but nothing quite the same. I'm using VLOOKUP to look at a part number column that's being imported. It all comes across in text format b/c some part numbers are alphanumeric (ex: 6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the issue b/c if I change the entire column to text I get #N/A back for cells in the column that should show a number in them, (ex:30). If I change the entire column to numeric format I get the same for cells that contain alphanumeric data (ex: 6H). Does anyone know of a solution that might address, seeing how there are two different types of data formats in the same column being referenced?!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup referencing one column that contains part numbers in b
Thanks Ron.
Unless I'm doing something wrong, that's not doing it. It doesn't make things look any different than it did before. MAN this is frustrating! "Ron Coderre" wrote: Try something like this: For ParNumbers in Col_A and a lookup table in Columns C:D Set all of the values in the Col_C of the lookup table Text. Then use a formula like this: B1: =VLOOKUP(A1&"",B:C,2,0) Appending an empty string to the numeric P/N's converts them to text. Appending an empty string to the text P/N's has no impact. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "crafty_girl" wrote: I'm seeing LOTS of posts that are close to this issue, but nothing quite the same. I'm using VLOOKUP to look at a part number column that's being imported. It all comes across in text format b/c some part numbers are alphanumeric (ex: 6H) and some are numeric (ex: 30). Nothing I've seen so far addresses the issue b/c if I change the entire column to text I get #N/A back for cells in the column that should show a number in them, (ex:30). If I change the entire column to numeric format I get the same for cells that contain alphanumeric data (ex: 6H). Does anyone know of a solution that might address, seeing how there are two different types of data formats in the same column being referenced?!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a column of numbers, with a condition | New Users to Excel | |||
Sum/average numbers in column A dependant on value in column B | Excel Worksheet Functions | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS | Excel Discussion (Misc queries) |