VLOOKUP PROBS!!!

Mar 5th, 2002, 02:11 PM

HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

=VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

Please let me know if u need more info. Thanks a lot!!

IML · Mar 5th, 2002, 02:13 PM

Quote:

On 2002-03-05 13:11, Anonymous wrote:
HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

=VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

Please let me know if u need more info. Thanks a lot!!

Do you get a value error or an n/a error?

Mark W. · Mar 5th, 2002, 02:16 PM

Your VLOOKUPs are returning non-numeric data to which you are applying the minus operator. That's why you're getting the #VALUE! error. I noticed that you didn't supply the 4th (optional) argument to your VLOOKUPs so they're not performing an exact match. Is that what you really want? Also, you're performing 2 VLOOKUPs when only one is necessary...

=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})

[ This Message was edited by: Mark W. on 2002-03-05 13:17 ]

[ This Message was edited by: Mark W. on 2002-03-05 13:20 ]

cnickerson · Mar 5th, 2002, 02:40 PM

I tried that formula with the same result. It is giving me zeros when I have data, and the same #VALUE! when there is none.

What it is doing is subtracting the original number projected for the beginning of the month from what has been built, giving me the balance as to what will be built. I am getting the #VALUE! error all the time.

Mark W. · Mar 5th, 2002, 02:47 PM

Of course, my formula gave the same #VALUE! error. The problem isn't the formula... (I believe) its the contents of the 5th and/or 7th column of your lookup table. What's in those columns?

[ This Message was edited by: Mark W. on 2002-03-05 13:50 ]

Mar 5th, 2002, 02:53 PM

Mark;

Help me understand the function of the {bracketed} items.

thanks

Scott

Quote:

On 2002-03-05 13:16, Mark W. wrote:
Your VLOOKUPs are returning non-numeric data to which you are applying the minus operator. That's why you're getting the #VALUE! error. I noticed that you didn't supply the 4th (optional) argument to your VLOOKUPs so they're not performing an exact match. Is that what you really want? Also, you're performing 2 VLOOKUPs when only one is necessary...

=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})

[ This Message was edited by: Mark W. on 2002-03-05 13:17 ]

[ This Message was edited by: Mark W. on 2002-03-05 13:20 ]

Mark W. · Mar 5th, 2002, 03:23 PM

Quote:

On 2002-03-05 13:53, Anonymous wrote:
Mark;

Help me understand the function of the {bracketed} items.

thanks

Scott

Scott, the use of an array constant as the 3rd VLOOKUP (Col_index_num) argument means I want to return values from those columns -- all at once! I should have been more explicit about the nature of this formula because once you use an array constant as the 3rd argument it should be entered as an array formula...

{=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})}

...and, array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

So... having said that... this VLOOKUP should then return a 2-element, horizontal array which I multiply by {1,-1} and then sum. {=SUM({1,2}*{1,-1})} is the same as saying, =SUM(1,-2) or =1-2, but allows me to do it more economically -- with 1 VLOOKUP. Depending on the length of the lookup table and the type of lookup (exact or approximate) VLOOKUPs can be quite "expensive"!

[ This Message was edited by: Mark W. on 2002-03-05 14:29 ]

cnickerson · Mar 5th, 2002, 03:34 PM

Sorry if I'm being redundant, but what I'm trying to communicate is that sometimes the value that vlookup is searching for (b10) is not in the column because it's value is zero. I'm trying to get it to relay that zero onto the original sheet but only #VALUE! comes up. Hope that helps.

cnickerson · Mar 5th, 2002, 03:41 PM

To clarify further, some months we don't make work orders for a specific product because we don't need it. I still want it to post as 0 on my sheet, but the referred part and quantity don't exist. I'm just trying to get it to "default" to zero if nothing is there because any other calculations based on that cell come up with, yes, #VALUE!..........

Aladin Akyurek · Mar 5th, 2002, 03:47 PM

Quote:

On 2002-03-05 13:11, Anonymous wrote:
HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to.

=VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

Please let me know if u need more info. Thanks a lot!!

Try the following, specifically for a value in B10 that gives you a #VALUE error:

in some cell enter and note the result:

=VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)

in another cell enter and note result:

VLOOKUP(B10,REM.TXT!$B$1:N$700,7)

Would you post the noted results in the follow up?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 5th, 2002, 02:11 PM	#1
Guest Posts: n/a	HI, I do am a novice in excel and can't figure out how to return a zero value using vlookup IF the info isn't there. I import a .txt doc in excel to calculate the remaining work orders for product that month. If there is no work order for that item I just get #VALUE! when I'd like to have a zero. Here is my formula where REM.TXT is the doc refered to. =VLOOKUP(B10,REM.TXT!$B$1:$J$700,5)-VLOOKUP(B10,REM.TXT!$B$1:N$700,7) Please let me know if u need more info. Thanks a lot!!

Mar 5th, 2002, 02:16 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Your VLOOKUPs are returning non-numeric data to which you are applying the minus operator. That's why you're getting the #VALUE! error. I noticed that you didn't supply the 4th (optional) argument to your VLOOKUPs so they're not performing an exact match. Is that what you really want? Also, you're performing 2 VLOOKUPs when only one is necessary... =SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1}) [ This Message was edited by: Mark W. on 2002-03-05 13:17 ] [ This Message was edited by: Mark W. on 2002-03-05 13:20 ]

Mar 5th, 2002, 02:40 PM	#4
cnickerson New Member Join Date: Mar 2002 Posts: 10	I tried that formula with the same result. It is giving me zeros when I have data, and the same #VALUE! when there is none. What it is doing is subtracting the original number projected for the beginning of the month from what has been built, giving me the balance as to what will be built. I am getting the #VALUE! error all the time.

Mar 5th, 2002, 02:47 PM	#5
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Of course, my formula gave the same #VALUE! error. The problem isn't the formula... (I believe) its the contents of the 5th and/or 7th column of your lookup table. What's in those columns? [ This Message was edited by: Mark W. on 2002-03-05 13:50 ]

Mar 5th, 2002, 03:34 PM	#8
cnickerson New Member Join Date: Mar 2002 Posts: 10	Sorry if I'm being redundant, but what I'm trying to communicate is that sometimes the value that vlookup is searching for (b10) is not in the column because it's value is zero. I'm trying to get it to relay that zero onto the original sheet but only #VALUE! comes up. Hope that helps.

Mar 5th, 2002, 03:41 PM	#9
cnickerson New Member Join Date: Mar 2002 Posts: 10	To clarify further, some months we don't make work orders for a specific product because we don't need it. I still want it to post as 0 on my sheet, but the referred part and quantity don't exist. I'm just trying to get it to "default" to zero if nothing is there because any other calculations based on that cell come up with, yes, #VALUE!..........