#N/A - - Can someo help me get this fixed?

scobad

Board Regular
Joined
Feb 7, 2005
Messages
164
Formula: =VLOOKUP(E2:E9000,Taken10!$A2:$H$900,7,FALSE)

If it is #N/A

I would like it to return o (zero)

I am trying to sum ytd hours for a earning. and The N/A is messing me up
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
=IF(ISNA(VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE)),"",VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE))

Best regards

Richard

Edit: I noticed you were using a range (E2:E900) rather than a single cell, so I modified the Vlookup
 

scobad

Board Regular
Joined
Feb 7, 2005
Messages
164
Almost

Now - it returns a BLANK?

When I sum that row, a few columns over - It returns #VALUE

That is whay I was thinking of puting a ZERO in there?
 

crobertson

New Member
Joined
Nov 14, 2005
Messages
32
Try this

=IF(ISNA(VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE)),"0",VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE))


Let me know if this helps!
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

Re: Almost

scobad said:
Now - it returns a BLANK?

When I sum that row, a few columns over - It returns #VALUE

That is whay I was thinking of puting a ZERO in there?

To sum with #N/A in your range

=SUMIF(A1:A10,"<>#N/A")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
scobad said:
Formula: =VLOOKUP(E2:E9000,Taken10!$A2:$H$900,7,FALSE)

If it is #N/A

I would like it to return o (zero)

I am trying to sum ytd hours for a earning. and The N/A is messing me up

Keep the results of

=VLOOKUP(E2,Taken10!$A$2:$H$900,7,0)

as is if you can and invoke:

=SUMIF(Range,"<>#N/A")

for summing, a setup that is a lot cheaper.
 

scobad

Board Regular
Joined
Feb 7, 2005
Messages
164

ADVERTISEMENT

I like this - but...it does not return 0

=IF(ISNA(VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE)),"0",VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE))



I would like that cell to actual have a ZERO in it? then my sum across would work?
 

scobad

Board Regular
Joined
Feb 7, 2005
Messages
164
As I copied this down - All the ones that had values are gone:

So - In Column N I would like to report a ZERO if is unfound
Than I can sum the ROW..
 

scobad

Board Regular
Joined
Feb 7, 2005
Messages
164
=IF(ISNA(VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE)),"0",VLOOKUP(E2,Taken10!$A2:$H$900,7,FALSE))


That is what I am using....Not working...
 

Watch MrExcel Video

Forum statistics

Threads
1,119,272
Messages
5,577,133
Members
412,769
Latest member
VK12345
Top