Vlookup Problem

[Belinda7237]

My Vlookup formula is:

=VLOOKUP(D81,'Week 2 Dataset'!G:O,9,0)

How would I write this differently so that when a result doesnt match i get
the cell to be left blank instead of #NA?

I need to incoprate an if but am not sure how to write it?

Thanks!

 

[Don Guillett]

Look in the help index for ISNA

 

[Gary''s Student]

=IF(ISERROR(VLOOKUP(D81,'Week 2 Dataset'!G:O,9,0)),"",VLOOKUP(D81,'Week 2
Dataset'!G:O,9,0))

 

[T. Valko]

Another one:

=IF(COUNTIF('Week 2 Dataset'!G:G,D81),VLOOKUP(D81,'Week 2
Dataset'!G:O,9,0),"")

If you're using Excel 2007:

=IFERROR(VLOOKUP(D81,'Week 2 Dataset'!G:O,9,0),"")

Note that this will trap *all* errors, not just #N/A.

 

[Dave]

Hi Biff,
I've seen you do this a couple of times lately - ie use an IF function
without an operator in the criteria. In this case, when the COUNTIF returns
1, the first option is chosen; when it returns 0, the second option is
chosen. Does the IF function just recognize a zero as FALSE, and anything
else as TRUE?.
Regards - Dave.

 

[Belinda7237]

thank you all for your help - in using this formula and copying it down the
column, it seems to work on the items that are NA, however, the items that
have a value instead of returning the looked up value return NA?

 

[T. Valko]

Does the IF function just recognize a zero as FALSE,

and anything else as TRUE?.

Well, not "anything else".

0 is evaluated as FALSE and *any number other than 0* is evaluated as TRUE.

0 = FALSE
1 = TRUE
-1.0255678 = TRUE
0.000000001 = TRUE
100000 = TRUE
TEXT = #VALUE! error

 

[Dave]

Thanks - Dave.