Hi,

As you know, when VLOOKUP uses "FALSE" for Range_lookup and no exact match
is made, Excel produces an output "#N/A".

This is messing up formulae that are based on the output of cells where this
formula is used, where then produce the same "#N/A".

I have tried using an If function in conjunction with VLOOKUP but it did not
produce the desired effect. Can someone please tell me if this is possible
(and I have made a mistake) or is there some other way.

Thanks in advance for any feedback.

Sam

--
Sam H. Carson

Before one can tell you what is possible with something other than an
(unspecified) IF() function, it's necessary for you to actually say what
"the desired effect" is...

You can return a null string, e.g.:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"", VLOOKUP(A1,J:K,2,FALSE))

or you can change "" to 0 if you need a zero value, etc.

In article ,
Sam H. Carson wrote:

Hi,

As you know, when VLOOKUP uses "FALSE" for Range_lookup and no exact match
is made, Excel produces an output "#N/A".

This is messing up formulae that are based on the output of cells where this
formula is used, where then produce the same "#N/A".

I have tried using an If function in conjunction with VLOOKUP but it did not
produce the desired effect. Can someone please tell me if this is possible
(and I have made a mistake) or is there some other way.

Thanks in advance for any feedback.

Sam

The IF statement should look something like this:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

This states that if your VLOOKUP formula returns the #N/A error, then return
a 0, if not, then return the results of your VLOOKUP formula. You could
replace the 0 with "" depending what you want in place of the #N/A error.

HTH,
Elkar


"Sam H. Carson" wrote:


Hi,

As you know, when VLOOKUP uses "FALSE" for Range_lookup and no exact match
is made, Excel produces an output "#N/A".

This is messing up formulae that are based on the output of cells where this
formula is used, where then produce the same "#N/A".

I have tried using an If function in conjunction with VLOOKUP but it did not
produce the desired effect. Can someone please tell me if this is possible
(and I have made a mistake) or is there some other way.

Thanks in advance for any feedback.

Sam

--
Sam H. Carson

Thanks Elkar, that's perfect! Exactly what I was looking for.

Much appreciated.

--
Sam H. Carson


"Elkar" wrote:

The IF statement should look something like this:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

This states that if your VLOOKUP formula returns the #N/A error, then return
a 0, if not, then return the results of your VLOOKUP formula. You could
replace the 0 with "" depending what you want in place of the #N/A error.

HTH,
Elkar


"Sam H. Carson" wrote:


Hi,

As you know, when VLOOKUP uses "FALSE" for Range_lookup and no exact match
is made, Excel produces an output "#N/A".

This is messing up formulae that are based on the output of cells where this
formula is used, where then produce the same "#N/A".

I have tried using an If function in conjunction with VLOOKUP but it did not
produce the desired effect. Can someone please tell me if this is possible
(and I have made a mistake) or is there some other way.

Thanks in advance for any feedback.

Sam

--
Sam H. Carson

Thanks JE. It will never cease to amaze me where you guys get this code from!

--
Sam H. Carson


"JE McGimpsey" wrote:

Before one can tell you what is possible with something other than an
(unspecified) IF() function, it's necessary for you to actually say what
"the desired effect" is...

You can return a null string, e.g.:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"", VLOOKUP(A1,J:K,2,FALSE))

or you can change "" to 0 if you need a zero value, etc.

In article ,
Sam H. Carson wrote:

Hi,

As you know, when VLOOKUP uses "FALSE" for Range_lookup and no exact match
is made, Excel produces an output "#N/A".

This is messing up formulae that are based on the output of cells where this
formula is used, where then produce the same "#N/A".

I have tried using an If function in conjunction with VLOOKUP but it did not
produce the desired effect. Can someone please tell me if this is possible
(and I have made a mistake) or is there some other way.

Thanks in advance for any feedback.

Sam