removing #N/A

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
Hi All

Looking for a way to remove this error when data not found.

Thanks in advance, Paul
fixit test.xls
CDEFG
2end of run reviewDie No.end of run review2ND DIE INBACK UP DIES
3#N/A1077B 71070 101073 15-180
4#N/A1607A 17-201059 29-3001607A 13-16
5#N/A1175 11-141520C 1-200
61057A 17-181057A 17-186008A 1500
7#N/A1496B 5-61147 11-1200
8#N/A1056A 19-201153 15-2000
9#N/A1687A 11027 7-801283B 23-24
101153 15-201153 15-201057A 17-1800
11#N/A1504D 3-41416A 3-400
121187B 25-261187B 25-261575A 1-200
13#N/A1533E 51187B 23-2400
14#N/A1431A 7-81187B 25-2600
15#N/A1522B 3-41682A 1-200
16#N/A1094 5-81680A 1-200
17#N/A1016A 46-471685A 100
18#N/A1234 37-481660A 1-400
191520C 1-21520C 1-21686A 1-200
Sheet1
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
=if(isna(VLOOKUP(D3,$E$3:$E$28,1,FALSE)),"",(VLOOKUP(D3,$E$3:$E$28,1,FALSE)))

Will return a blank when N/A

:cool:
 
Upvote 0
Code:
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:="#N/A!"
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents

That would be the macro route. Other way would be to simply Copy and Paste Special as a Value to rid yourself of the formula. Then you could do a Replace (Ctrl + H) and just type in #N/A in the first box and a space in the second box.
 
Upvote 0
Invoke: Either...

=IF(ISNUMBER(MATCH(D3,$E$3:$E$28,0)),D3,"")

or...

=CHOOSE(ISNUMBER(MATCH(D3,$E$3:$E$28,0))+1,"",D3)

instead of:

=VLOOKUP(D3,$E$3:$E$28,1,FALSE)
 
Upvote 0

Forum statistics

Threads
1,217,433
Messages
6,136,599
Members
450,021
Latest member
Jlopez0320

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top