Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cmatise
 
Posts: n/a
Default 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
  #2   Report Post  
Michael
 
Posts: n/a
Default

Hi Try
=IF(ISNA(VLOOKUP(Cell,Range,column,FALSE)),"0",VLO OKUP(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

  #3   Report Post  
Myrna Larson
 
Posts: n/a
Default

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


On Mon, 10 Jan 2005 19:19:03 -0800, "Cmatise"
wrote:

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


  #4   Report Post  
Cmatise
 
Posts: n/a
Default

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",VLO OKUP(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

  #5   Report Post  
Biff
 
Posts: n/a
Default

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",VLO OKUP

(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
.



  #6   Report Post  
Sir Paul
 
Posts: n/a
Default

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",VLO OKUP(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

  #7   Report Post  
Biff
 
Posts: n/a
Default

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",VLO OKUP

(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
.

  #8   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in 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",VLO OKUP(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


  #9   Report Post  
 
Posts: n/a
Default

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"
wrote:

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

replace #N/A with a 0

.

  #10   Report Post  
JulieD
 
Posts: n/a
Default

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

wrote in message
...
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"
wrote:

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

replace #N/A with a 0

.





  #11   Report Post  
Ola
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup finds a blank, but returns a zero - HELP! flummoxed Excel Discussion (Misc queries) 6 January 18th 05 04:15 PM
troubleshoot vlookup returning #N/A dillon Excel Worksheet Functions 1 December 2nd 04 04:32 PM
Vlookup returns incorrect match Smichaud Excel Discussion (Misc queries) 2 November 30th 04 11:51 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
vlookup returns n/a Todd L. Excel Worksheet Functions 1 November 5th 04 10:05 PM


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"