VLOOKUP - Adding a non valur parameter

[Mark_richardson28]

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

 

[Brian from Maui]

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]

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]

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]

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]

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

 

[Mark_richardson28]

Thats worked fantastically.

Thanks for all your help Brian