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 15:56, cnickerson wrote:
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.

Okay, let's break this problem down. But, 1st you must dismiss the notion that the formula is returning #VALUE! because it didn't find the lookup value in your table. Believe me (or at least believe the Excel Help topic for VLOOKUP worksheet function) that (when using the formula that I supplied, {=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})} ) only a #N/A error is returned when no match is found. Since a #N/A isn't being returned then either VLOOKUP is finding #VALUE! in columns 5 and/or 7, or a text value (e.g., "cat", "red", "") that can't be coerced into a number is being returned.

1. I noticed that your table range ($B$1:$J$700) includes row 1. Is row 1 the 1st entry in the table or headers that label your table columns?

2. Enter the formulas, =ISTEXT(F1) and =ISTEXT(H1), into unused columns on row 1 and copy down to row 700. Do either of these formulas produce TRUE on any row? If so, on the formula bar select the cell reference within that ISTEXT function and report your finding in a reply posting.

Let's start there and see what you find.
This message was edited by Mark W. on 2002-03-06 06:57

 

[cnickerson]

Thanks for your help Mike, defintely helped me out!!! The report I was using did have text so I used IFCOUNT to find if the code was there in the first place. Seems to work great now. I appreciate all the posts that helped me out. Here's the function I used if you are interested.

=IF(COUNTIF(Rem.txt!$B$1:$J$700,B10)=1,SUM(VLOOKUP(B10,Rem.txt!$B$1:$J$700,{5,7},0)*{1,-1}),0)