VLOOKUP

[Ed]

I need to do a VLOOKUP where if there is no match found I do not want the
value to be #N/A. How can I accomplish this?

T.I.A.
Ed

 

[J.E. McGimpsey]

one way:

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

use

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

 

[Mark Graesser]

Ed
You need to use an IF statement

=IF(ISNA(yourlookupformula),"",yourlookupformula

Good Luck
Mark Graesse
(e-mail address removed)

----- Ed wrote: ----

I need to do a VLOOKUP where if there is no match found I do not want th
value to be #N/A. How can I accomplish this

T.I.A
E

 

[Lance]

do a check for #N/A

=IF(ISNA(VLOOKUP(A1,C1:C4,1)),0,VLOOKUP(A1,C1:C4,1))

 

[mmackinnon]

-----Original Message-----
I need to do a VLOOKUP where if there is no match found I do not want the
value to be #N/A. How can I accomplish this?

T.I.A.
Ed




.
Use the if function

=if(iserr(vlookup(A3,Sheet1!a2:j98,3,0),0,vlookup

(A3,Sheet1!a2:j98,3,0))

 

[Jeff Glock]

You can accomplish this by nesting functions. In this case
use the conditional function IF and the information
function ISERROR.

For example: Given that "CAL" is the table being looked
for and "A5" is the cell containing the lookup reference
the following formula will return an empty set if the
value in "A5" is not found.

=IF(ISERROR(VLOOKUP(A5,CAL,2,FALSE)),"",VLOOKUP
(A5,CAL,2,FALSE))

Note that the parenthesis returns an empty set. If you
would prefer a zero or some other fixed value, like "Value
not found" insert that in the parenthesis.