Prev: Help with SUMIF & AND function
Next: SUMIF with area that is not a range
From: juliejg1 on 12 Mar 2010 09:35
I have the following formula in a cell:
=IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0)

Problem is that the results of this are used for calculations and K2
sometimes doesn't equal "STEEL" so I get an error. I've tried putting an
Iserror and have the error = 0 but am doing something wrong. Can you help?
From: Luke M on 12 Mar 2010 09:44
An error would occur if the VLOOKUP can not find a result. Perhaps this:
=IF($K2="STEEL",IF(ISERROR(VLOOKUP($B2,'Macos 12 Mo.
Data'!$A$4:$AU$326,40,FALSE)),0,VLOOKUP($B2,'Macos 12 Mo.
Data'!$A$4:$AU$326,40,FALSE)),0)

or

=IF(OR($K2<>"STEEL",ISERROR(VLOOKUP($B2,'Macos 12 Mo.
Data'!$A$4:$AU$326,40,FALSE))),0,VLOOKUP($B2,'Macos 12 Mo.
Data'!$A$4:$AU$326,40,FALSE))

--
Best Regards,

Luke M
"juliejg1" <juliejg1(a)discussions.microsoft.com> wrote in message
news:097C1C90-7B56-467A-AB77-E749B11991E6(a)microsoft.com...
>I have the following formula in a cell:
> =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0)
>
> Problem is that the results of this are used for calculations and K2
> sometimes doesn't equal "STEEL" so I get an error. I've tried putting an
> Iserror and have the error = 0 but am doing something wrong. Can you
> help?


From: Mike H on 12 Mar 2010 09:54
Hi,

The way the formula is set up if 'steel' isn't in B2 it will return zero.
What will give an error is not find the value in B2 in the lookup range and
you can get around that by checking if the lookup value is there before
executing the vlookup

=IF(COUNTIF('Macos 12 Mo.
Data'!$A$4:$A$326,$B2)>0,IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo.
Data'!$A$4:$AU$326,40,FALSE),0),"")


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"juliejg1" wrote:

> I have the following formula in a cell:
> =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0)
>
> Problem is that the results of this are used for calculations and K2
> sometimes doesn't equal "STEEL" so I get an error. I've tried putting an
> Iserror and have the error = 0 but am doing something wrong. Can you help?
From: juliejg1 on 12 Mar 2010 10:01

Thank you Mike....very helpful!


"Mike H" wrote:

> Hi,
>
> The way the formula is set up if 'steel' isn't in B2 it will return zero.
> What will give an error is not find the value in B2 in the lookup range and
> you can get around that by checking if the lookup value is there before
> executing the vlookup
>
> =IF(COUNTIF('Macos 12 Mo.
> Data'!$A$4:$A$326,$B2)>0,IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo.
> Data'!$A$4:$AU$326,40,FALSE),0),"")
>
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "juliejg1" wrote:
>
> > I have the following formula in a cell:
> > =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0)
> >
> > Problem is that the results of this are used for calculations and K2
> > sometimes doesn't equal "STEEL" so I get an error. I've tried putting an
> > Iserror and have the error = 0 but am doing something wrong. Can you help?
 | 
Pages: 1
Prev: Help with SUMIF & AND function
Next: SUMIF with area that is not a range