Combining Vlookup and an IF _NEED HELP

[Celeste]

I am using a VLookup (False) to search a different file for specific items,
the problem is that when it doesn't find the item, it enters "#N/A". In
the main file I need to total the columns, but cannot total them due to
the N/A's that are being inserted. I've tried nesting an IF (**<>), but it
keeps bringing up a blank on the cells that should pull a value.

Can anyone help???


Sincerely,

Frustrated

 

[J.E. McGimpsey]

I am using a VLookup (False) to search a different file for specific items,
the problem is that when it doesn't find the item, it enters "#N/A". In
the main file I need to total the columns, but cannot total them due to
the N/A's that are being inserted. I've tried nesting an IF (**<>), but it
keeps bringing up a blank on the cells that should pull a value.

Can anyone help???

One way:

Assume your VLOOKUP() is VLOOKUP(A1,J1:K100,2,FALSE):

=IF(ISNA(MATCH(A1,J1:J100,FALSE)),"",VLOOKUP(A1,J1:K100,2,FALSE))

Or, a little shorter to type, but not as fast in calculation:

=IF(COUNTIF(J1:J100,A1)=0,"",VLOOKUP(A1,J1:K100,2,FALSE))

Or, if you have named your lookup table, say "LTable":

=IF(ISNA(MATCH(A1,OFFSET(LTable,,,,1),FALSE)), "",
VLOOKUP(A1,LTable,2,FALSE))

 

[Jon Barchenger[MS]]

Good morning Celeste -

Try usng a variation the the following formula:

=IF(ISERROR(VLOOKUP(V73,Y50:Z62,2,0)),"",VLOOKUP(V73,Y50:Z62,2,0))

If the vlookup returns an error, like #N/A, the result will be a blank or nothing ( "" ).
If it is not an error it will complete the vlookup.

Let me know if you need further help with this issue.

Thanks,
Jon Barchenger
--------------------
From: "J.E. McGimpsey" <[email protected]>
Mail-Copies-To: nobody
Subject: Re: Combining Vlookup and an IF _NEED HELP
References: <[email protected]>
User-Agent: MT-NewsWatcher/3.3b1 (PPC Mac OS X)
Date: Mon, 01 Dec 2003 20:50:35 -0700
Message-ID: <[email protected]>
Newsgroups: microsoft.public.mac.office.excel
NNTP-Posting-Host: 65-103-217-205.bois.qwest.net 65.103.217.205
Lines: 1
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl!jemcgimpsey
Xref: cpmsftngxa06.phx.gbl microsoft.public.mac.office.excel:8493
X-Tomcat-NG: microsoft.public.mac.office.excel

I am using a VLookup (False) to search a different file for specific items,
the problem is that when it doesn't find the item, it enters "#N/A". In
the main file I need to total the columns, but cannot total them due to
the N/A's that are being inserted. I've tried nesting an IF (**<>), but it
keeps bringing up a blank on the cells that should pull a value.

Can anyone help???

One way:

Assume your VLOOKUP() is VLOOKUP(A1,J1:K100,2,FALSE):

=IF(ISNA(MATCH(A1,J1:J100,FALSE)),"",VLOOKUP(A1,J1:K100,2,FALSE))

Or, a little shorter to type, but not as fast in calculation:

=IF(COUNTIF(J1:J100,A1)=0,"",VLOOKUP(A1,J1:K100,2,FALSE))

Or, if you have named your lookup table, say "LTable":

=IF(ISNA(MATCH(A1,OFFSET(LTable,,,,1),FALSE)), "",
VLOOKUP(A1,LTable,2,FALSE))

 

[J.E. McGimpsey]

Good morning Celeste -

Try usng a variation the the following formula:

=IF(ISERROR(VLOOKUP(V73,Y50:Z62,2,0)),"",VLOOKUP(V73,Y50:Z62,2,0))

If the vlookup returns an error, like #N/A, the result will be a blank or
nothing ( "" ).
If it is not an error it will complete the vlookup.

Let me know if you need further help with this issue.

I generally advise using

=IF(ISNA(MATCH(V73,Y50:Y62,FALSE)),"",VLOOKUP(V73,Y50:Z62,2,0))

instead, since:

1) MATCH() is faster than VLOOKUP(). Not a big deal for a few
formulae, but if *lots* of copies it can affect calculation speed.

2) More importantly, using ISERROR() masks ALL errors. Since the
only error that the OP wants to ignore is #N/A, it doesn't make any
sense to block #VALUE!,#REF!,#NUM!,etc., which could indicate a
legitimate problem in the data. Hiding a real error may cause the
user to make decisions based on bad data, which is, IMO, far worse
than allowing the error to be thrown.

If you don't feel comfortable with ISNA(MATCH()), at least use

=IF(ISNA(VLOOKUP(V73,Y50:Z62,2,0)),"",VLOOKUP(V73,Y50:Z62,2,0))