Vlookup help

[willlobb]

Is there any way to get rid of the #N/A value that is returned when an item in the specified array cannot be found?
Thanks
Will

[Chris Davison]

have a look at the =ERROR.TYPE function which will give you a bit of grounding on the types of errors and how to spot/trap them

but for the #N/A error, I usually use the following (there's many different ways, hopefully you'll get other options too after this post) :

=IF(ISNA(VLOOKUP(your stuff)=TRUE),"",VLOOKUP(your stuff))

which is basically saying :

1) if your vlookup is bringing back an N/A error, then put nothing otherwise

2)proceed with your vlookup

[Aladin Akyurek]

On 2002-04-07 05:09, willlobb wrote:
Is there any way to get rid of the #N/A value that is returned when an item in the specified array cannot be found?
Thanks
Will

It's better to keep #N/A if you can. Otherwise, in the absence of any spec concerning the layout that is involved in your VLOOKUP-formula [e.g., the range that houses your lookup table, the cell (if any) that houses the lookup value, more importantly, the match-type (that is, TRUE or FALSE) that your formula uses]:

If match-type (the 4th arg of VLOOKUP) is FALSE, use:

=IF(COUNTIF(E2:E50,A2),VLOOKUP(A2,E2:H50,3,0),value-to-return-otherwise)

where E2:E50 is the first column of the lookup-table in E2:H50 and 0 as the 4th arg of the VLOOKUP function is the same as FALSE. Finally, the value-to-return-otherwise can be "" or 0, up to you to decide.

If match-type is TRUE, use:

=IF(ISNUMBER(MATCH(A2,E2:E50)),VLOOKUP(A2,E2:H50,3),value-to-return-otherwise)

where everything is the same as above, except for the 4th arg of VLOOKUP, here omitted. The omission means the same as 1 (or TRUE).

I might add that using 0 or 1 (instead of FALSE or TRUE) in lookup functions is a perfectly sound habit.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-07 08:13 ]

[willlobb]

thanks a lot for the replies, they were very helpful.
Will