#### BarnBrian

##### Board Regular
Is there any way that when I have Lookup in a range of cells and #N/A appears that insead of #N/A an instruction appears.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Peter_SSs

##### MrExcel MVP, Moderator
Is the error occurring because
a) the lookup value cell is empty?
b) the lookup value cell is not empty but the lookup value does not appear in the lookup range?
c) the cell that gets looked up in the lookup range already contains the #N/A error?

Here is one way that should work in all of the above cases, but there may be a simpler way, depending on the answer above:

#### BarnBrian

##### Board Regular
Option B is the situation

#### Peter_SSs

##### MrExcel MVP, Moderator
Option B is the situation
You haven't provided any details of ranges, how your sheet is operated etc, so let's suppose your current formula is ...
=VLOOKUP(A1,\$C\$2:\$D\$10,2,0)
... then a few options might be:

1. The formula already given above, which would become:
=IF(ISNA(VLOOKUP(A1,\$C\$2:\$D\$10,2,0)),"instruction",VLOOKUP(A1,\$C\$2:\$D\$10,2,0))
2. =IF(ISNUMBER(MATCH(A1,\$C\$2:\$C\$10)),VLOOKUP(A1,\$C\$2:\$D\$10,2,0),"instruction")
3. Use Data Validation in A1 to stop the user entering an entry that is not in C2:C10 in the first place.

Replies
5
Views
579
Replies
14
Views
357
Replies
11
Views
2K
Replies
1
Views
896
Replies
7
Views
945

1,190,558
Messages
5,981,690
Members
439,730
Latest member
gjvv

### 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.

### Which adblocker are you using?

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

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