Standard VLOOKUP but need to replace #N/A with blank

coffee11

New Member
Joined
Sep 26, 2004
Messages
30
Hi,

How do you change this VLOOKUP so that if the result is #N/A, and if the field is blank, the result will be either "no" or a blank field?

=VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
=IF(COUNTIF('PROCESS Trk'!$C$9:$C$53,A8)=0,"Not Found",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

Denis
 
Upvote 0
Hi,

Try..

=if(isna(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)),"",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

Hope this what u have been looking for....

Saurabh...
 
Upvote 0
Thanks thats great, but the blank fields that the sheet is looking at come up as date 00/01/1900, whereas I need them to be "Not Found" as well.

can you modify? or do I have to change my formatting?
 
Upvote 0
Thanks thats great, but the blank fields that the sheet is looking at come up as date 00/01/1900, whereas I need them to be "Not Found" as well.

can you modify? or do I have to change my formatting?
_________________
honda


Try...

=if(isna(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE)),"Not Found",VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,FALSE))

Saurabh...
 
Upvote 0
Does anybody know how to VLOOKUP dates without returning....

the empty fields in the VLOOKUP as 00/01/1900 (I want them to be empty)
 
Upvote 0
Re: Does anybody know how to VLOOKUP dates without returning

coffee11 said:
the empty fields in the VLOOKUP as 00/01/1900 (I want them to be empty)

If you installed the morefunc add-in...

=IF(ISNA(SETV(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,0))),"",IF(GETV(),GETV(),""))

If you can't use morefunc for some reason...

=IF(ISNA(V(VLOOKUP(A8,'PROCESS Trk'!$C$9:$O$53,13,0))),"",IF(V(),V(),""))

What follows is the code for the V() function and instruction how to add it to your workbook:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

To add this to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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