Vlookup #N/A error due to formatting
I'm linking a report worksheet to a data pull page, and in cell c4 in the
report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") which gives me 1530002038 as the result. This cell is formatted as General. I'm using vlookup on the report to find this item code in the product spec sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A error. I've used vlookup a million times, and I've had problems before with formatting. I've used the EXACT function and Frank Kabel's =CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these cell have identical information. Any ideas on what else I can check or correct to make the vlookup find the product code in the spec sheet? As always, any help is appreciated. |
Try
=VLOOKUP(--C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: I'm linking a report worksheet to a data pull page, and in cell c4 in the report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") which gives me 1530002038 as the result. This cell is formatted as General. I'm using vlookup on the report to find this item code in the product spec sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A error. I've used vlookup a million times, and I've had problems before with formatting. I've used the EXACT function and Frank Kabel's =CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these cell have identical information. Any ideas on what else I can check or correct to make the vlookup find the product code in the spec sheet? As always, any help is appreciated. |
Thanks, Peo, that worked! Can you tell me what the "--" in front of the C4
do to the formula? I've never seen that used before. Thanks again for your help and quick response. Patrick "Peo Sjoblom" wrote: Try =VLOOKUP(--C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: I'm linking a report worksheet to a data pull page, and in cell c4 in the report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") which gives me 1530002038 as the result. This cell is formatted as General. I'm using vlookup on the report to find this item code in the product spec sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A error. I've used vlookup a million times, and I've had problems before with formatting. I've used the EXACT function and Frank Kabel's =CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these cell have identical information. Any ideas on what else I can check or correct to make the vlookup find the product code in the spec sheet? As always, any help is appreciated. |
It just forces the value in C4 to be numeric, you could use 0+C4 or 1*C4 as
well It is quite frequently used as coercing TRUE/FALSE into 1/0 in sumproduct formulas Since the formula you have in C4 will return a text value, you could probably simplify that formula to =--RIGHT('15300 UPC Data Pull'!C4,10) or if indeed the text function is needed you can do the numeric coercing directly =--TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") then use =VLOOKUP(C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: Thanks, Peo, that worked! Can you tell me what the "--" in front of the C4 do to the formula? I've never seen that used before. Thanks again for your help and quick response. Patrick "Peo Sjoblom" wrote: Try =VLOOKUP(--C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: I'm linking a report worksheet to a data pull page, and in cell c4 in the report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") which gives me 1530002038 as the result. This cell is formatted as General. I'm using vlookup on the report to find this item code in the product spec sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A error. I've used vlookup a million times, and I've had problems before with formatting. I've used the EXACT function and Frank Kabel's =CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these cell have identical information. Any ideas on what else I can check or correct to make the vlookup find the product code in the spec sheet? As always, any help is appreciated. |
you could also try Data-Text to Columns on the column, then it should work
"Peo Sjoblom" wrote: It just forces the value in C4 to be numeric, you could use 0+C4 or 1*C4 as well It is quite frequently used as coercing TRUE/FALSE into 1/0 in sumproduct formulas Since the formula you have in C4 will return a text value, you could probably simplify that formula to =--RIGHT('15300 UPC Data Pull'!C4,10) or if indeed the text function is needed you can do the numeric coercing directly =--TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") then use =VLOOKUP(C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: Thanks, Peo, that worked! Can you tell me what the "--" in front of the C4 do to the formula? I've never seen that used before. Thanks again for your help and quick response. Patrick "Peo Sjoblom" wrote: Try =VLOOKUP(--C4,oldupc,5,FALSE) Regards, Peo Sjoblom "Patrick_KC" wrote: I'm linking a report worksheet to a data pull page, and in cell c4 in the report, I have the formula: =TEXT(RIGHT('15300 UPC Data Pull'!C4,10),"0") which gives me 1530002038 as the result. This cell is formatted as General. I'm using vlookup on the report to find this item code in the product spec sheet, using the formula: =VLOOKUP(C4,oldupc,5,FALSE), but I get the #N/A error. I've used vlookup a million times, and I've had problems before with formatting. I've used the EXACT function and Frank Kabel's =CODE(MID($A$1,COLUMN(A:A),1)) formula (thanks, Frank) to determine these cell have identical information. Any ideas on what else I can check or correct to make the vlookup find the product code in the spec sheet? As always, any help is appreciated. |
All times are GMT +1. The time now is 09:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com