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

[Guest]

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?

 

[Guest]

Hi,

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

HTH
Regards from Brazil
Marcelo

"njuneardave" escreveu:

 

[Guest]

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

 

[Guest]

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?

 

[Dave Peterson]

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.)