#N/A help

[coltheplumb]

Hi guys I have this vlookup formula in cell G90
=VLOOKUP(F90,$CX$13:$CY$16,2)*10
If there is nothing in cell F90 it shows a #N/A in cell G90 , is there anyway of doing something to get rid of the #N/A ??
All help appreciated
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Colin X

 

[T. Valko]

Hi guys I have this vlookup formula in cell G90
=VLOOKUP(F90,$CX$13:$CY$16,2)*10
If there is nothing in cell F90 it shows a #N/A in cell G90 , is there anyway of doing something to get rid of the #N/A ??
All help appreciated
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Colin X

One way...

=IF(F90="","",VLOOKUP(F90,$CX$13:$CY$16,2)*10)

 

[coltheplumb]

Thank You T. Valko

Colin X

 

[Aladin Akyurek]

Hi guys I have this vlookup formula in cell G90
=VLOOKUP(F90,$CX$13:$CY$16,2)*10
If there is nothing in cell F90 it shows a #N/A in cell G90 , is there anyway of doing something to get rid of the #N/A ??
All help appreciated
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Colin X

The way you have set up the formula requires that $CX$13:$CY$16 is sorted on $CX$13:$CX$16 in ascending order.

If the sorting assumption is correct, the following would prevent #N/A as result:

=IF(F90 < MIN($CX$13:$CX$16), 0, VLOOKUP(F90,$CX$13:$CY$16,2,1)*10)

Otherwise, try:

On Excel 2007 or later...

=IFERROR(VLOOKUP(F90,$CX$13:$CY$16,2,0),0)*10

On all versions...

=IF(ISNUMBER(MATCH(F90,$CX$13:$CX$16,0)),VLOOKUP(F90,$CX$13:$CY$16,2,1)*10,0)

SumIf can be here appropriate...

=SUMIF($CX$13:$CX$16,F90,$CY$13:$CY$16)*10

 

[T. Valko]

Thank You T. Valko

Colin X

You're welcome. Thanks for the feedback!