Re: Why am I getting a #VALUE! Error?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: JeffJ (JeffJ.163l28_at_excelforum-nospam.com)
Date: 05/11/04 

Date: Tue, 11 May 2004 10:21:46 -0500

Thanks, Mark.

Interestingly, while the following formula returns a #VALUE! error:

=IF(AND(OR(H5=1,H5=2),H2="...3/12...",H3="12
inch"),VLOOKUP(H6,lookup!$C$3:$D$19,2,0),IF(AND(H5=3,H2="...3/12...",H3="12
inch"),VLOOKUP(H6,lookup!$E$3:$F$19,2,0),"")) + IF($H$16=$C$34,$H$17,0)
+ IF($H$18=$C$34,$H$19,0) + IF($H$20=$C$34,$H$21,0) +
IF($H$22=$C$34,$H$23,0) + IF($H$24=$C$34,$H$25,0) +
IF($H$26=$C$34,$H$27,0) + IF($H$28=$C$34,$H$29,0)

This one (from a different cell) returns 0 instead (which is
preferable):

=IF(AND(OR($H$6="38X12",$H$6="40X12",$H$6="42X12",$H$6="44X12",$H$6="46X12",$H$6="48X12",$H$6="50X12",$H$6="52X12",$H$6="54X12",$H$6="56X12",$H$6="58X12",$H$6="60X12",$H$6="62X12",$H$6="64X12",$H$6="66X12",$H$6="68X12",$H$6="70X12"),$H$2="...3/12...",$H$3="12
inch"),COUNTIF($H$7:$H$16,"=E-120-D")) + IF($H$16=$C$36,$H$17,0) +
IF($H$18=$C$36,$H$19,0) + IF($H$20=$C$36,$H$21,0) +
IF($H$22=$C$36,$H$23,0) + IF($H$24=$C$36,$H$25,0) +
IF($H$26=$C$36,$H$27,0) + IF($H$28=$C$36,$H$29,0)

Not sure why that is, but I see that my first formula contains VLOOKUP,
whereas the second does not.

Note that I only get #VALUE! or 0 if the formula cells are blank.

Anyway, thanks for your reply.

Jeff Jenkins
Ocala, FL (SO happy I moved away from Miami!)
jojh@aol.com
jeffjenkins777@earthlink.net
http://lightningfingers.tripod.com 

---
Message posted from http://www.ExcelForum.com/

Relevant Pages

  • Re: Find text and return - NOOB question
    ... It sound to me that you might be able to use a simple Vlookup with wildcards, as long as you're only looking for a *single* match. ... If the contents of A1 are not found in B434, then the first formula will return ... You could easily test for A1 being empty. ...
    (microsoft.public.excel.misc)
  • Re: matching values between worksheets
    ... Assuming your first formula is in Sheet1!B1, ... It is important to add the FALSE as 4th argument to VLOOKUP. ... Sheet 2, column A, copy B:F from that row into Sheet1. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: HELP! PLEASE
    ... Put the first formula in F2 and copy down. ... Put the second formula in G2 and copy down. ... Again this uses VLOOKUP to match ... Part Number and Qty that I have versus what the customer has. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Grading Marks I
    ... Any value <49 returned by either formula's VLOOKUP will result in #N/A. ... In your first formula: ... In your second formula: ... part of (IF ISERR) or something else: ...
    (microsoft.public.excel)