VLOOKUP - Adding a non valur parameter

Mark_richardson28

New Member
Joined
Sep 29, 2006
Messages
36
I am using a VLOOKUP in one of my spreadsheets. I need it to say "NoData" should there be no data in the field it is referencing from.


Can anyone help?

THanks
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
I am using a VLOOKUP in one of my spreadsheets. I need it to say "NoData" should there be no data in the field it is referencing from.


Can anyone help?

THanks

Not sure what you're referring too, but try one of the following,

=IF(ISNA(VLOOKUP(A1,B1:C10,2,0),"No Data")),VLOOKUP(A1,B1:C10,2,0)

Or

IF(A1="","No Data",VLOOKUP(A1,B1:C10,2,0))

Adjust ranges....
 

Mark_richardson28

New Member
Joined
Sep 29, 2006
Messages
36
This is my Vlookup

=VLOOKUP($C9,'Raw data - Jan Martin'!$A4:$L43,2,FALSE)

Tried both of those suggestions, but neither worked
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
This is my Vlookup

=VLOOKUP($C9,'Raw data - Jan Martin'!$A4:$L43,2,FALSE)

Tried both of those suggestions, but neither worked

In what instance would you like "No Data" displayed? If C9 is empty or if C9 is not found in A4:A43?
 

Mark_richardson28

New Member
Joined
Sep 29, 2006
Messages
36

ADVERTISEMENT

The data is inserted to the "raw data spreadsheet" by a from box. So there is no garauntee that there will be a value there. so using excel as a mini database. The vlookup is referencing it by date, which is in cell C9. If the v lookup cant find anything it is saying #N/A which i want it to say "No Data".

cheers
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
The data is inserted to the "raw data spreadsheet" by a from box. So there is no garauntee that there will be a value there. so using excel as a mini database. The vlookup is referencing it by date, which is in cell C9. If the v lookup cant find anything it is saying #N/A which i want it to say "No Data".

cheers

My apologies, misplaced paren,

=IF(ISNA(VLOOKUP(A1,B1:C10,2,0)),"No Data",VLOOKUP(A1,B1:C10,2,0))

adjust ranges and worksheet name.

As an aside, you may want to check for any leading/trailing spaces when you import data in your lookup column. An extra space will return a #N/A
 

Forum statistics

Threads
1,137,204
Messages
5,680,166
Members
419,887
Latest member
Vasokir

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
Top