When VLOOKUP returns a #N/A How can you get it to replace #N/A wi.

When VLOOKUP returns a #N/A How can you get it to replace #N/A with a 0

Hi Try
=IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP(Cell,Range,Column,False))

HTH
Michael

"Cmatise" wrote:

> When VLOOKUP returns a #N/A How can you get it to replace #N/A with a 0

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))


On Mon, 10 Jan 2005 19:19:03 -0800, "Cmatise"
<[email protected]> wrote:

>When VLOOKUP returns a #N/A How can you get it to replace #N/A with a 0

I Still Cant get it to work.
Let Me show you what I want to Do.

I want to look for the date thats on sheet (weekly Draw B1)
on sheet (Install Panels A4:A43 and when I find a date that matches return
the $ amount that is in the same row in the 3rd column F3:F43

Sheet (Weekly Draw)
A B
1 Date of Draw 12/23/2005
2 Materials Draw
3 Rec. Metal
4 Total Draw


Total Weekly Payroll
A B F
3 Date Qty Installed Draw
4 1/5/05 5.93 $354.22
5 $-
6 $-
7 $-
8 $-
9 $-

"Michael" wrote:

> Hi Try
> =IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP(Cell,Range,Column,False))
>
> HTH
> Michael
>
> "Cmatise" wrote:
>
> > When VLOOKUP returns a #N/A How can you get it to replace #N/A with a 0

Hi1

Try this:

=SUMPRODUCT(--('INSTALL PANELS'!A4:A43='WEEKLY DRAW'!
B1),'INSTALL PANELS'!F4:F43)

Biff

>-----Original Message-----
>I Still Cant get it to work.
>Let Me show you what I want to Do.
>
>I want to look for the date thats on sheet (weekly Draw
B1)
>on sheet (Install Panels A4:A43 and when I find a date
that matches return
>the $ amount that is in the same row in the 3rd column
F3:F43
>
>Sheet (Weekly Draw)
> A B
>1 Date of Draw 12/23/2005
>2 Materials Draw
>3 Rec. Metal
>4 Total Draw
>
>
>Total Weekly Payroll
> A
B F
>3 Date Qty Installed Draw
>4 1/5/05 5.93 $354.22
>5 $-
>6 $-
>7 $-
>8 $-
>9 $-
>
>"Michael" wrote:
>
>> Hi Try
>> =IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP
(Cell,Range,Column,False))
>>
>> HTH
>> Michael
>>
>> "Cmatise" wrote:
>>
>> > When VLOOKUP returns a #N/A How can you get it to
replace #N/A with a 0
>.
>

I would have used an =IF( statement first, before going into my =VLOOKUP(

=if(b1="",0,vlookup(b1,criteria name,column ref))

I hope I explained this to you correctly, as I used this format regularly
and it works.

"Cmatise" wrote:

> I Still Cant get it to work.
> Let Me show you what I want to Do.
>
> I want to look for the date thats on sheet (weekly Draw B1)
> on sheet (Install Panels A4:A43 and when I find a date that matches return
> the $ amount that is in the same row in the 3rd column F3:F43
>
> Sheet (Weekly Draw)
> A B
> 1 Date of Draw 12/23/2005
> 2 Materials Draw
> 3 Rec. Metal
> 4 Total Draw
>
>
> Total Weekly Payroll
> A B F
> 3 Date Qty Installed Draw
> 4 1/5/05 5.93 $354.22
> 5 $-
> 6 $-
> 7 $-
> 8 $-
> 9 $-
>
> "Michael" wrote:
>
> > Hi Try
> > =IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP(Cell,Range,Column,False))
> >
> > HTH
> > Michael
> >
> > "Cmatise" wrote:
> >
> > > When VLOOKUP returns a #N/A How can you get it to replace #N/A with a 0

Hi!

That's fine if B1 is blank. If B1 isn't blank and isn't
found in the lookup table, you'll still get #N/A. Of
course, if the lookup value will always be found in the
lookup table then that's not a problem.

If you use the IF ISNA formula, you cover both
possibilities.

Biff

>-----Original Message-----
>I would have used an =IF( statement first, before going
into my =VLOOKUP(
>
>=if(b1="",0,vlookup(b1,criteria name,column ref))
>
>I hope I explained this to you correctly, as I used this
format regularly
>and it works.
>
>"Cmatise" wrote:
>
>> I Still Cant get it to work.
>> Let Me show you what I want to Do.
>>
>> I want to look for the date thats on sheet (weekly Draw
B1)
>> on sheet (Install Panels A4:A43 and when I find a date
that matches return
>> the $ amount that is in the same row in the 3rd column
F3:F43
>>
>> Sheet (Weekly Draw)
>> A B
>> 1 Date of Draw 12/23/2005
>> 2 Materials Draw
>> 3 Rec. Metal
>> 4 Total Draw
>>
>>
>> Total Weekly Payroll
>> A
B F
>> 3 Date Qty Installed Draw
>> 4 1/5/05 5.93 $354.22
>> 5 $-
>> 6 $-
>> 7 $-
>> 8 $-
>> 9 $-
>>
>> "Michael" wrote:
>>
>> > Hi Try
>> > =IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP
(Cell,Range,Column,False))
>> >
>> > HTH
>> > Michael
>> >
>> > "Cmatise" wrote:
>> >
>> > > When VLOOKUP returns a #N/A How can you get it to
replace #N/A with a 0
>.
>

Did you try

=IF(ISNA(VLOOKUP('Weekly Draw'!B1,'Install Panels'!$A$4:$F$43
,6,FALSE)),"",VLOOKUP('Weekly Draw'!B1,'Install Panels'!$A$4:$F$43
,6,FALSE))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Cmatise" <[email protected]> wrote in message
news:[email protected]...
> I Still Cant get it to work.
> Let Me show you what I want to Do.
>
> I want to look for the date thats on sheet (weekly Draw B1)
> on sheet (Install Panels A4:A43 and when I find a date that matches return
> the $ amount that is in the same row in the 3rd column F3:F43
>
> Sheet (Weekly Draw)
> A B
> 1 Date of Draw 12/23/2005
> 2 Materials Draw
> 3 Rec. Metal
> 4 Total Draw
>
>
> Total Weekly Payroll
> A B F
> 3 Date Qty Installed Draw
> 4 1/5/05 5.93 $354.22
> 5 $-
> 6 $-
> 7 $-
> 8 $-
> 9 $-
>
> "Michael" wrote:
>
> > Hi Try
> >
=IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLOOKUP(Cell,Range,Column,Fal
se))
> >
> > HTH
> > Michael
> >
> > "Cmatise" wrote:
> >
> > > When VLOOKUP returns a #N/A How can you get it to replace #N/A with a
0

This works always!

=IF(ISERROR(VLOOKUP(A5;A:A;8;FALSE));0;VLOOKUP
(A5;A:A;8;FALSE))

Mr.G
- Up North

>-----Original Message-----
>=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))
>
>
>On Mon, 10 Jan 2005 19:19:03 -0800, "Cmatise"
><[email protected]> wrote:
>
>>When VLOOKUP returns a #N/A How can you get it to
replace #N/A with a 0
>
>.
>

Hi

ISERROR is very broad and will trap all errors not just the #NA error
i would suggest using

> =IF(ISNA(VLOOKUP(A5;A:A;8;FALSE));0;VLOOKUP
> (A5;A:A;8;FALSE))

instead

Cheers
JulieD

<[email protected]> wrote in message
news:[email protected]...
> This works always!
>
> =IF(ISERROR(VLOOKUP(A5;A:A;8;FALSE));0;VLOOKUP
> (A5;A:A;8;FALSE))
>
> Mr.G
> - Up North
>
>>-----Original Message-----
>>=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))
>>
>>
>>On Mon, 10 Jan 2005 19:19:03 -0800, "Cmatise"
>><[email protected]> wrote:
>>
>>>When VLOOKUP returns a #N/A How can you get it to
> replace #N/A with a 0
>>
>>.
>>

Hi,

Try this: =LOOKUPV(C1,A1:B100,2,0,0)

But first
1. Press Alt+F11. Insert Module. Copy and Paste the below.

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Optional Error_Msg)
LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_value)
If IsError(LookupV) And Not IsMissing(Error_Msg) Then LookupV =
Error_Msg
End Function

The LOOKUPV formula is shorter and is faster then VLOOKUP
Make sure the VBA code is 4 rows!

Regards,
Ola