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!!

 

[Mark W.]

On 2002-03-05 14:34, cnickerson wrote:
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.

This is not the cause of your #VALUE! error. Take a look at the Help Index topics for "What does the error #VALUE! mean?" and "VLOOKUP worksheet function". VLOOKUP produces #N/A when it can't find it's lookup value -- not #VALUE!.

 

[cnickerson]

#VALUE! for both functions. Column 5 is the number of items to be built, and column 7 is the number of items that HAVE BEEN built. My prob. is when there is nothing for VLOOKUP to find, I'd like it to report zero.

Thanks again!!!

 

[cnickerson]

The B10 that my formula refers to is a SPED.PBKA prod. code. The vlookup then goes to REM.TXT, finds that code and looks at columns 5&7 to determine the amount. Is my problem that is not finding the SPED.PBKA and stops there?? If so, I again need it to report zero if that product code isn't on REM.TXT

 

[Mark W.]

It does not stop there!!! You haven't supplied a 4th argument to your VLOOKUPs so it assumes that your lookup table is sorted and it "grabs" the next closest entry. Do you understand this distinction of how VLOOKUPs work?

 

[cnickerson]

Yes, I did read about that in the help file, but wasn't sure how it was applied. My problem is I didn't set this up in the first place and have a limited knowledge of Excel. Is VLOOKUP the ideal function for what I'm trying to accomplish?

Thanks man.

 

[Aladin Akyurek]

On 2002-03-05 14:55, cnickerson wrote:
#VALUE! for both functions. Column 5 is the number of items to be built, and column 7 is the number of items that HAVE BEEN built. My prob. is when there is nothing for VLOOKUP to find, I'd like it to report zero.

Thanks again!!!

I think I understand what you want. It's of importance that we track down why you get #VALUE! error before giving you the desired zeroes in case of failure.

I asked for the info to see whether one of the VLOOKUPs returned a space that would lead to a #VALUE error.

If you get correct results for some lookup values and for others not, there must be something wrong with REM.TXT.

 

[cnickerson]

If there are values in REM.TXT then the VLOOKUP reports the numbers correctly, however if the product code it is looking for is not on the report...........#VALUE! But otherwise it works fine.

 

[Mark W.]

On 2002-03-05 15:13, cnickerson wrote:
Yes, I did read about that in the help file, but wasn't sure how it was applied. My problem is I didn't set this up in the first place and have a limited knowledge of Excel. Is VLOOKUP the ideal function for what I'm trying to accomplish?

Thanks man.

First, let's go back and use my suggested array formula...

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

...this formula 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.

Please reconfirm that this is producing a #VALUE! error. I trust that REM.TXT is a worksheet in the same workbook as your lookup value. Is that the case?
This message was edited by Mark W. on 2002-03-05 15:23

 

[Guest]

Got it. Now THAT is cool.

On 2002-03-05 14:23, Mark W. wrote:

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]

Yes Mark, I did enter it with Ctrl,Shift, and Enter, and came up with the same #VALUE! I know that because I tried entering it normally without any results. The REM.TXT is a file I open along with the regular worksheet (amongst other files). I don't think it's in the same workbook. When I open REM.TXT I have to set the columns etc, so everything lines up. But again, if there isn't a product code for VLOOKUP to find it returns #VALUE! I know I'm sayin the same thing over and over, but that's the case.