VLOOKUP and if/then statement

[rraymond]

I want to use a VLOOKUP to find values that are greater than zero. If the value is zero or non-existent, I want the vlookup to display a zero. Can someone help?

 

[Jonmo1]

I want to use a VLOOKUP to find values that are greater than zero. If the value is zero or non-existent, I want the vlookup to display a zero. Can someone help?

Welcome to the board..

As far as I know, that is the default behavior.

If the result of the vlookup is 0 or blank, then the vlookup returns 0


or do you mean if the lookup value is blank or 0?
=VLOOKUP(A1,B:C,2,False)

Do you mean if A1 is blank or 0?

 

[T. Valko]

I want to use a VLOOKUP to find values that are greater than zero. If the value is zero or non-existent, I want the vlookup to display a zero. Can someone help?

VLOOKUP will already do what you want so VLOOKUP may NOT be the function you want to use!

Can you post some sample data and tell us what result you expect?

 

[texasalynn]

Welcome to MrExcel board...

does this work

Excel Workbook
	A	B
1	value >0 found	4
2
3
Sheet1

Excel 2003

Cell Formulas
Range	Formula
A1	=IF(COUNTIF(B:B,">0"),"value >0 found",0)

 

[rraymond]

Well, the default behavior isn't exactly working the way I want it to. In some cases, the search string I'm looking for is missing. So... if the vlookup can't find the search string, I want a zero, but what I'm getting is that #N/A symbol. Basically what I want it to do is to return the value it finds - if the search string is there - if the value is over zero. If not, then I want it to just record a zero. Make sense?

 

[Jonmo1]

Post your current formula please.

And what version of Excel do you have?

 

[rraymond]

Here's my formula:

=VLOOKUP("SouthwestChevroletPreston Chevrolet",'April 2011 Raw Data'!$1:$65536,9,FALSE)

I'm looking for sales data for Preston Chevrolet in a geographic zone. The problem is, Preston Chevrolet may not sell in this SW zone, but I need to record that one way or another. So if there are no sales in SW, then I need the formula to give me a zero.

 

[rraymond]

I'm using Excel 2003.

 

[T. Valko]

Well, the default behavior isn't exactly working the way I want it to. In some cases, the search string I'm looking for is missing. So... if the vlookup can't find the search string, I want a zero, but what I'm getting is that #N/A symbol. Basically what I want it to do is to return the value it finds - if the search string is there - if the value is over zero. If not, then I want it to just record a zero. Make sense?

Assuming the value being returned is numeric.

Try something like this.

For ANY version of Excel...

=LOOKUP(1E100,CHOOSE({1,2},0,VLOOKUP(...)))

For Excel versions 2007 and later...

=IFERROR(VLOOKUP(...),0)

Fill in the details of the VLOOKUP function.

 

[Jonmo1]

Try

=IF(ISNA(MATCH("SouthwestChevroletPreston Chevrolet",'April 2011 Raw Data'!$A:$A,0)),0,INDEX('April 2011 Raw Data'!I:I,MATCH("SouthwestChevroletPreston Chevrolet",'April 2011 Raw Data'!$A:$A,0)))


This can also be made simpler...
What type of data does the vlookup return...
Text or Number?