If Vlookup Question

[Brent]

Hi,

I have created the following equation using VLOOKUP...

=VLOOKUP(D26,'Sales Averages'!$A$4:$M$5000,4,FALSE)

The result varies depends on my data, but I would like any result that is $0
to equal "***"

Does anyone have any ideas?

Thanks so much for your help!!

 

[Squeaky]

Hi Brent,

If you are only wanting results that are under a certain amount you can use:

=if(VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE)>A1,0,VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE))

I placed the "upper limit" in cell A1.

If you are wanting ALL values under that amount you should use a filter.

Squeaky.

 

[Geoff]

Try this:

=IF(VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE)=0,"***",VLOOKUP(D26,'Sales
Averages'!$A$4:$M$5000,4,FALSE))

 

[Dave Peterson]

You may want to keep the result 0, but show the asterisks.

You could select the range with this formula and use:

Format|Cells|Number Tab
Select Custom Category
type:
$#,##0.00;-$#,##0.00;"***"

The format is: positive;negative;zero;text

Change the format to what you really like for the non-zero values.

You may want to try just hitting the $ on the formatting toolbar (xl2003). This
applies a currency format that shows a hyphen for 0's.

By displaying *** (or -), you may find that subsequent arithmetic formulas stay
nice and easy.