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!

Look in the help index for ISNA

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Belinda7237" wrote in message
...
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!

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

--
Gary''s Student - gsnu200791

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.


--
Biff
Microsoft Excel MVP


"Belinda7237" wrote in message
...
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!

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.

"T. Valko" wrote:

Another one:

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

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200791

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

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
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.

"T. Valko" wrote:

Another one:

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

Thanks - Dave.