Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
How do I use conditional formatting for multiple rows? | Excel Worksheet Functions |