VLOOKUP DEFAULT ERROR (WOULD LIKE TO CHANGE)

[grev]

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

 

[RichardS]

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

 

[maxflia10]

Read this thread

http://www.mrexcel.com/board/viewtopic.php?topic=17280&forum=2

The 0 0 at the end of your example is row number and false. Replace the first 0 with the row to lookup

Why are you getting the error message?
This message was edited by maxflia10 on 2002-09-05 16:46

 

[Juan Pablo González]

That VLOOKUP with a 5th parameter is my ELOOKUP formula, wich is under "experimentation". Check it out here

http://www.mrexcel.com/board/viewtopic.php?topic=21141&forum=2

 

[grev]

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.

 

[RichardS]

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

 

[RichardS]

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

 

[Juan Pablo González]

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")

 

[grev]

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

 

[Aladin Akyurek]

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.