Vlookup formula help required 1

[gnanas]

I have two X-cel tables, using Vlookup formula I try to extract information from first table into the second one for comparison and totalling up . In certain cells I get #N/A because values are not available which is perfectly correct. However I am unable to sum up the column becaues of #N/A cells.

I wish to get "zero" instead of #N/A so that I can total up the column. Can somone help me to develop a formula incorporating Vlookup and converting #N/A into "zero"

 

[fneily]

Maybe you could use this:

=IF(ISERROR(VLookup(D14,Table,2)),0,VLookup(D14,Table,2))

ISERROR will return True if there's an error like #N/A. So the IF statement will put a 0 otherwise just put the VLookup value.

Neil

 

[sfvb]

Using the VBA syntax examples, you would want to structure your equation like this:

=IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_num,range_lookup )),"",VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

For a real example: If my equation looked like this:

=VLOOKUP(E7,B6:C10,2,FALSE)

Excel might return #N/A
But if I use:

=IF(ISNA(VLOOKUP(E7,B6:C10,2,FALSE)),"",VLOOKUP(E7,B6:C10,2,FALSE))

The cell will not display anything if there is an #N/A error, which will allow you're SUM function to work.

Basically this formula is saying, "If I'm going to get an error doing what I want to do, don't display anything, otherwise go ahead and do it."

 

[gnanas]

Hi Fneily & Sfvb

Thanks for your prompt reply It worked

Gnanas

 

[sfvb]

Looks like Neil beat me to the punch. He's using

ISERROR

, which is a catchall for just about any error. I was using

ISNA

, which tests specifically for the #N/A error.

As far as I know the VLOOKUP function only throws #N/A errors. But, other functions may throw different errors, so it is good to know what errors you can catch, as you may want a different outcome depending on what type of error Excel throws. Here's a list of the different errors you can test for in a formula. (At least with Excel 2000):

ISBLANK
ISERR
ISERROR
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISREF
ISTEXT

 

[xlbo]

sfvb - vlookup can also give #REF errors - only when the number of columns to look across is larger than the data area.eg the lookup range is A:H and the cols across (3rd argument) is 9 or more

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[sfvb]

xlbo - Thanks. I haven't run across that one.