VLOOKUP PROBS!!!

[Guest]

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]

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.]

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]

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.]

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

 

[Guest]

Mark;

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

thanks

Scott

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.]

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]

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]

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]

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?