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?!! |
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?!! |
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?!! |
All times are GMT +1. The time now is 09:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com