VLOOKUP DEFAULT ERROR (WOULD LIKE TO CHANGE)

grev

New Member
Joined
Sep 4, 2002
Messages
21
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
Thanks again for your help. It worked. Are there any special or unfortunate constraints with the countif or the formula you sent? Thanks again.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top