Need Help with Vlookup

[Joefried]

Hi There

thanks for looking and trying to help

i would like a formula if:

the vlookup's answer is N/A leave blank
meaning if he can't find a match leave it blank

example

=VLOOKUP(A:A,SHEET2!A:B,2,FALSE)
now if he cant find a match for the look up meaning in raw A there is no match leave it blank

Thank you so much

 

[T. Valko]

Hi There

thanks for looking and trying to help

i would like a formula if:

the vlookup's answer is N/A leave blank
meaning if he can't find a match leave it blank

example

=VLOOKUP(A:A,SHEET2!A:B,2,FALSE)
now if he cant find a match for the look up meaning in raw A there is no match leave it blank

Thank you so much

Typically, the first argument to VLOOKUP is a SINGLE cell reference, not an entire column. Like this...

=VLOOKUP(A1,SHEET2!A:B,2,0)

Now, if you want to trap any errors...

If you're using Excel 2007 or later:

=IFERROR(VLOOKUP(A1,SHEET2!A:B,2,0),"")

Note that that will trap ALL errors not just #N/A errors.

For other versions:

IF(ISNA(VLOOKUP(A1,SHEET2!A:B,2,0)),"",VLOOKUP(A1,SHEET2!A:B,2,0))

 

[Joefried]

thank you thank you thank you
you're a life saver

 

[T. Valko]

thank you thank you thank you
you're a life saver

You're welcome. Thanks for the feedback!

 

[Joefried]

sorry but there is another problem now because there is a error in the background i can't do a sum which will include that box because the answer would be #VALUE how do we resolve that?

 

[T. Valko]

sorry but there is another problem now because there is a error in the background i can't do a sum which will include that box because the answer would be #VALUE how do we resolve that?

Try this...

Book1
	A
1	49
2	27
3	60
4	23
5	#VALUE!
6	11
7	94
8	6
9	15
10	85
Sheet1

=SUMIF(A1:A10,"<1E100")

Result = 370