Vlookup = #n/a to be ""
 

I'm using this formula, which is mostly working for what I need.

=VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)

Basically, if it finds what's in B57 on the data!b3:E52 table, it's
producing the name match, which is what I need. However, if there's no match,
it's producing the #N/A. How could I get the #N/A to not show ?

Thanks,

Steve

Vlookup = #n/a to be ""
 

=IF(ISNA(VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"",V LOOKUP(B57,'data'!B$3:E$52,1,FALSE))

Or, if you have Excel 2007:

=IFERROR(VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



"Steve" wrote in message ...
| I'm using this formula, which is mostly working for what I need.
|
| =VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)
|
| Basically, if it finds what's in B57 on the data!b3:E52 table, it's
| producing the name match, which is what I need. However, if there's no match,
| it's producing the #N/A. How could I get the #N/A to not show ?
|
| Thanks,
|
| Steve

Vlookup = #n/a to be ""
 

Maybe this suffices:
=IF(COUNTIF(data!B$3:B$52,B57),B57,"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote:
I'm using this formula, which is mostly working for what I need.

=VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)

Basically, if it finds what's in B57 on the data!b3:E52 table, it's
producing the name match, which is what I need. However, if there's no match,
it's producing the #N/A. How could I get the #N/A to not show ?

Thanks,

Steve

Vlookup = #n/a to be ""
 

Perfect. Thanks guys. Both this solution and the ISNA worked exactly as a
needed.

Much appreciated.
Thanks again,

Steve

"Max" wrote:

Maybe this suffices:
=IF(COUNTIF(data!B$3:B$52,B57),B57,"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Steve" wrote:
I'm using this formula, which is mostly working for what I need.

=VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)

Basically, if it finds what's in B57 on the data!b3:E52 table, it's
producing the name match, which is what I need. However, if there's no match,
it's producing the #N/A. How could I get the #N/A to not show ?

Thanks,

Steve

Vlookup = #n/a to be ""
 

Hello,

You're forcing Excel to potentially do the vlookup twice, why not
place your formula in a hidden area of your worksheet like IV1, then
reference that formula in your target cell:

=IF(ISNA(IV1),"",IV1)


HTH,
JP


On Oct 20, 9:37 am, "Niek Otten" wrote:
=IF(ISNA(VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"",V LOOKUP(B57,'data'!B$3:E$5*2,1,FALSE))

Or, if you have Excel 2007:

=IFERROR(VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Steve" wrote in ...

| I'm using this formula, which is mostly working for what I need.
|
| =VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)
|
| Basically, if it finds what's in B57 on the data!b3:E52 table, it's
| producing the name match, which is what I need. However, if there's no match,
| it's producing the #N/A. How could I get the #N/A to not show ?
|
| Thanks,
|
| Steve

Vlookup = #n/a to be ""
 

Hi Max,

I think what you meant was =IF(COUNTIF(data!B$3:B
$52,B57),VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")

?


--JP

On Oct 20, 9:45 am, Max wrote:
Maybe this suffices:
=IF(COUNTIF(data!B$3:B$52,B57),B57,"")
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---



"Steve" wrote:
I'm using this formula, which is mostly working for what I need.

=VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)

Basically, if it finds what's in B57 on the data!b3:E52 table, it's
producing the name match, which is what I need. However, if there's no match,
it's producing the #N/A. How could I get the #N/A to not show ?

Thanks,

Steve- Hide quoted text -

- Show quoted text -

Vlookup = #n/a to be ""
 

Note that the OP looks up in 1 column only....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JP" wrote in message oups.com...
| Hi Max,
|
| I think what you meant was =IF(COUNTIF(data!B$3:B
| $52,B57),VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")
|
| ?
|
|
| --JP
|
| On Oct 20, 9:45 am, Max wrote:
| Maybe this suffices:
| =IF(COUNTIF(data!B$3:B$52,B57),B57,"")
| --
| Max
| Singaporehttp://savefile.com/projects/236895
| xdemechanik
| ---
|
|
|
| "Steve" wrote:
| I'm using this formula, which is mostly working for what I need.
|
| =VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)
|
| Basically, if it finds what's in B57 on the data!b3:E52 table, it's
| producing the name match, which is what I need. However, if there's no match,
| it's producing the #N/A. How could I get the #N/A to not show ?
|
| Thanks,
|
| Steve- Hide quoted text -
|
| - Show quoted text -
|
|

Vlookup = #n/a to be ""
 

=IF(ISNA(VLOOKUP(B57,data!B$3:E$52,1,FALSE)),"",VL OOKUP(B57,data!B$3:E$52,1,FALSE))


Gord Dibben MS Excel MVP

On Sat, 20 Oct 2007 06:24:00 -0700, Steve
wrote:

I'm using this formula, which is mostly working for what I need.

=VLOOKUP(B57,'data'!B$3:E$52,1,FALSE)

Basically, if it finds what's in B57 on the data!b3:E52 table, it's
producing the name match, which is what I need. However, if there's no match,
it's producing the #N/A. How could I get the #N/A to not show ?

Thanks,

Steve

Vlookup = #n/a to be ""
 

Niek,
In that case what do you think is the better formula, COUNTIF or
VLOOKUP?

--JP

On Oct 20, 10:40 am, "Niek Otten" wrote:
Note that the OP looks up in 1 column only....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JP" wrote in ooglegroups.com...

| Hi Max,
|
| I think what you meant was =IF(COUNTIF(data!B$3:B
| $52,B57),VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")
|
| ?
|

Vlookup = #n/a to be ""
 

In that case what do you think is the better formula,
COUNTIF or VLOOKUP?

COUNTIF (see Max's reply)

NB: COUNTIF will evaluate TEXT numbers and NUMERIC numbers as being equal
while VLOOKUP will not.


--
Biff
Microsoft Excel MVP


"JP" wrote in message
ups.com...
Niek,
In that case what do you think is the better formula, COUNTIF or
VLOOKUP?

--JP

On Oct 20, 10:40 am, "Niek Otten" wrote:
Note that the OP looks up in 1 column only....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JP" wrote in
ooglegroups.com...

| Hi Max,
|
| I think what you meant was =IF(COUNTIF(data!B$3:B
| $52,B57),VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"")
|
| ?
|