question about excessive code in replacing #N/A when using VLOOKUP
 

so....here is the common consensus of the code to replace the #N/A VLOOKUP
result with a blank space:

=if(isna(vlookup(...),"",vlookup())


Why can't you simplify that code to look like this:

=if(isna(vlookup(...),"",Sheet1!A3)

assuming the A3 cell of Sheet1 is the search key.


What is wrong with simplifying the code to say that instead of copying the
vlookup method call again?

question about excessive code in replacing #N/A when using VLOOKUP
 

Hi,

on the simplify model, the formula will always return A3 if the Isna is true

HTH
Regards from Brazil
Marcelo

"njuneardave" escreveu:

so....here is the common consensus of the code to replace the #N/A VLOOKUP
result with a blank space:

=if(isna(vlookup(...),"",vlookup())


Why can't you simplify that code to look like this:

=if(isna(vlookup(...),"",Sheet1!A3)

assuming the A3 cell of Sheet1 is the search key.


What is wrong with simplifying the code to say that instead of copying the
vlookup method call again?

question about excessive code in replacing #N/A when using VLOOKUP
 

Because you will get A3 as your value rather than the value that VLOOKUP will
return.

"njuneardave" wrote:

so....here is the common consensus of the code to replace the #N/A VLOOKUP
result with a blank space:

=if(isna(vlookup(...),"",vlookup())


Why can't you simplify that code to look like this:

=if(isna(vlookup(...),"",Sheet1!A3)

assuming the A3 cell of Sheet1 is the search key.


What is wrong with simplifying the code to say that instead of copying the
vlookup method call again?

question about excessive code in replacing #N/A when using VLO
 

but wouldnt A3 be the SAME value that VLOOKUP would return....so in essence,
making it equal?

Also, if you copied that equation down into other cells (say, A4-A53), you
would get back A4-A53, respectively.....not A3 everytime....same
thing...right?


Or, am I missing an assocation here?

"Toppers" wrote:

Because you will get A3 as your value rather than the value that VLOOKUP will
return.

"njuneardave" wrote:

so....here is the common consensus of the code to replace the #N/A VLOOKUP
result with a blank space:

=if(isna(vlookup(...),"",vlookup())


Why can't you simplify that code to look like this:

=if(isna(vlookup(...),"",Sheet1!A3)

assuming the A3 cell of Sheet1 is the search key.


What is wrong with simplifying the code to say that instead of copying the
vlookup method call again?

question about excessive code in replacing #N/A when using VLO
 

The values aren't always in the same order:

If I have data like this:

a 1
b 2
c 3
d 4
e 5

my =vlookup() formula could look like:
=vlookup("A",a1:b5,2,false)
and it would return 1 which could be a formula as simple as: =B1

But if my data looked like:

1 2
3 4
x 7
a 1
aa 2
ab 3
b 4
c 9

I would want my formula to be =B4

The purpose of the =vlookup() formula is to find a match in the first column and
retrieve the value in the other column.

And that data could be as mixed up as possible. Excel will look for the match
and use what it finds (or return N/A.)



njuneardave wrote:

but wouldnt A3 be the SAME value that VLOOKUP would return....so in essence,
making it equal?

Also, if you copied that equation down into other cells (say, A4-A53), you
would get back A4-A53, respectively.....not A3 everytime....same
thing...right?

Or, am I missing an assocation here?

"Toppers" wrote:

Because you will get A3 as your value rather than the value that VLOOKUP will
return.

"njuneardave" wrote:

so....here is the common consensus of the code to replace the #N/A VLOOKUP
result with a blank space:

=if(isna(vlookup(...),"",vlookup())


Why can't you simplify that code to look like this:

=if(isna(vlookup(...),"",Sheet1!A3)

assuming the A3 cell of Sheet1 is the search key.


What is wrong with simplifying the code to say that instead of copying the
vlookup method call again?

--

Dave Peterson