Vlookup = #n/a to be ""

[Guest]

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

 

[Niek Otten]

=IF(ISNA(VLOOKUP(B57,'data'!B$3:E$52,1,FALSE),"",VLOOKUP(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



| 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

 

[Guest]

Maybe this suffices:
=IF(COUNTIF(data!B$3:B$52,B57),B57,"")

 

[Guest]

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

Much appreciated.
Thanks again,

Steve

 

[JP]

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

 

[JP]

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

 

[Niek Otten]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[JP]

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

--JP

 

[T. Valko]

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.