# #N/A ?#2

#### crtas1

##### Board Regular
ALADIN GAVE ME THIS TO USE YESTERDAY TO MAKE ALL #N/A's IN A SPREADSHEET 0.

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

I am not sure what to put where the ... are. Ususally my VLOOKUP looks like =VLOOKUP(B2,A1:D1,2,FALSE)
Am I doing (VLOOKUP("N/A",a1:d1,?,false)????

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
put exactly what you'd normally put there! Have you tried it? What results are you getting?

This message was edited by PaddyD on 2002-09-11 05:42

What I typed is what I would normally would put in. Where the ? is is the part I am not sure of. In VLOOKUP it usually pulls something back into the cell you have it in. I don't want it to pull anything back so I am not sure what to put there or if the #N/A part is correct either. I am getting formula errors.
This message was edited by crtas1 on 2002-09-11 06:15

On 2002-09-11 05:36, crtas1 wrote:
ALADIN GAVE ME THIS TO USE YESTERDAY TO MAKE ALL #N/A's IN A SPREADSHEET 0.

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

I am not sure what to put where the ... are. Ususally my VLOOKUP looks like =VLOOKUP(B2,A1:D1,2,FALSE)
Am I doing (VLOOKUP("N/A",a1:d1,?,false)????

I see I goofed... try:

=IF(ISNA(SETV(VLOOKUP(B2,A1:D1,2,0))),0,GETV())

The intention is not to compute twice: That's why we use SETV and GETV from the morefunc add-in.

Oopps - and I just saw the setv & ploughed straight on in - must be time for bed

=IF(ISNA(SETV(VLOOKUP("#N/A",N7:Z12,2,0))),0,GETV())
I did get a 0 back when I entered this in the cell that this formula was in. I want it to put zeros in all the cells that have an #N/A in them. Will I have to put this in front of every cell that could have a #N/A? I already have this in there:

=VLOOKUP(Sheet1!\$A\$2,\$A\$1:\$E\$369,4,FALSE)
which is what is creating the #n/a. If I do need to put yours with mine how would I put them together. I was thinking more like a cell outside of the formulas that would scan the worksheet and change them automatically.

On 2002-09-11 06:23, crtas1 wrote:
=IF(ISNA(SETV(VLOOKUP("#N/A",N7:Z12,2,0))),0,GETV())
I did get a 0 back when I entered this in the cell that this formula was in. I want it to put zeros in all the cells that have an #N/A in them. Will I have to put this in front of every cell that could have a #N/A? I already have this in there:

=VLOOKUP(Sheet1!\$A\$2,\$A\$1:\$E\$369,4,FALSE)
which is what is creating the #n/a. If I do need to put yours with mine how would I put them together. I was thinking more like a cell outside of the formulas that would scan the worksheet and change them automatically.

Just replace

=VLOOKUP(Sheet1!\$A\$2,\$A\$1:\$E\$369,4,FALSE)

with

=IF(ISNA(SETV(VLOOKUP(Sheet1!\$A\$2,\$A\$1:\$E\$369,4,0))),0,GETV())

That worked perfectly!! I appreciate your help on this. Thanks again!!

Replies
6
Views
1K
Replies
5
Views
386
Replies
1
Views
815
Replies
9
Views
592
Replies
4
Views
56

1,216,095
Messages
6,128,792
Members
449,468
Latest member
AGreen17

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back