# VLOOKUP DEFAULT ERROR (WOULD LIKE TO CHANGE)

#### grev

##### New Member
I would like to change the default error message when using the VLOOKUP function. The #N/A is too busy on a large spread sheet. I saw somewhere to use this =vlookup(x,table,c,0,0) stated result was a 0 would result. Did not work. Need help

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try

=IF(ISNA(VLOOKUP(C,TABLE,B,0)=TRUE),"ERROR",VLOOKUP(C,TABLE,B,0))

Replace "ERROR" with whatever you want the NA value to return.

HTH

Richard

Richard thanks for your reply. It seems simple enough however I try this and I am still getting the infamous #N/A. I have sorted the table and I still get the #N/A. Look forward to advice.

Juan,

I was going to mention Aladin and your ELOOKUP option, but thought I would stick with the existing built in MS functionality. Hopefully MS will listen to you guys, and make ELOOKUP a reality.

Richard

Grev,

Can you post the formula you are getting an error on, so we can have a closer look at it? There may be other issues.

Richard

And, if you want to stick to MS builtin resources, I wouldn't do the ISNA(VLOOKUP()) approach, because you're doing a double calculation there of VLOOKUP. Instead, using COUNTIF to see if there's a match in there, and if not, return the "If error" result, something like

=IF(COUNTIF(\$C\$1:\$C\$10,A1),VLOOKUP(A1,\$C\$1:\$E\$10,3,0),"Error")

Thanks again for your help. It worked. Are there any special or unfortunate constraints with the countif or the formula you sent? Thanks again.

On 2002-09-05 17:36, grev wrote:
Thanks again for your help. It worked. Are there any special or unfortunate constraints with the countif or the formula you sent? Thanks again.

No. The other options which are deemed to be faster on larger sets of data are:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LTable,0,1),0)),VLOOKUP(lookup-value,LTable,col-index,0),0)

If you install the morefunc add-in, you can use:

=IF(ISNA(SETV(VLOOKUP(lookup-value,LTable,col-index,0))),0,GETV())

LTable is the name that you can assign to the range housing your lookup table.

Do a search on this site to find out where to get the morefunc add-in.

Replies
10
Views
624
Replies
5
Views
400
Replies
1
Views
546
Replies
3
Views
334
Replies
1
Views
1K

1,221,239
Messages
6,158,716
Members
451,510
Latest member
kegnazmach

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