# If "#N/A" is returned, replace this value with "NO DATA")

#### iansammcluckie

##### New Member
Hi.

I am using the following vlookup:
=VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0).

When the formula is not finding a value, it is returning #N/A.
I wish to replace this #N/A with "NO DATA".

Thank you.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### lynxbci

##### Board Regular
Hi,

=if(ISNA(VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0)),"No Data",
VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0))

Kev

##### MrExcel MVP
Hi.

I am using the following vlookup:
=VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0).

When the formula is not finding a value, it is returning #N/A.
I wish to replace this #N/A with "NO DATA".

Thank you.
Excel 2007 and beyond:
Rich (BB code):
``=IFERROR(VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0),"NO DATA")``
Prior versions:
Rich (BB code):
``````=IF(ISNUMBER(MATCH(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$A\$213,0)),
VLOOKUP(LEFT(\$A2,8)&"*",Dashboard_EA!\$A\$199:\$P\$213,5,0),
"NO DATA")``````

#### iansammcluckie

##### New Member
SOLVED_Re: If "#N/A" is returned, replace this value with "NO DATA")

Thanks for your help, it is solved.

Ian

1,106,580
Messages
5,512,178
Members
408,882
Latest member
Clarab9