 # Trouble w VLOOKUP (later in formula) displaying "0"s or #VALUE!

• ...if I change the last zero to "" for example.

Code
``=IF(\$AS\$2="No","",ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE),0))``

Works perfect if the initial IF is false; it displays a BLANK. But I can't edit the last zero so that an error or problem in the formula displays a BLANK as well.

Let me know if you can help.

Thanks!

Also posted here.

• The last 0 is a parameter in the ROUND() function telling Excel how many decimals to round to. You can't change that to a "". Are you sure that there is a number in AJ2 and the VLOOKUP is resulting in a number?

Maybe try the VLOOKUP on it's own to see what it yields.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Try this:
=IFERROR(IF(\$AS\$2="No","",ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE),0)),"")

Ali Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right. • Thank you for trying!

This option adds a "0" when \$AS\$2="No" unfortunately. I can't use the formatting options either, because sometimes there's a legitimate zero in the cell I'm adding the formula. I just need to figure out how to make this work {\$AS\$2="No","",} and actually not add a zero. I don't THINK it matters but jic, \$AJ\$2 is not a number. It's a value similar to this...G2-1.

• Sorry, I meant is AI2 a number? Check also column 19 of the ParameterTable to see if it is numeric and if there are any VALUE errors in there.

Somehow this part \$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE) is not resulting as a number that the ROUND function can use.

Can you post a sample workbook showing the problem?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Thanks SO much for your patience!

I got confused there. That's exactly right! It's when it uses the formula. It adds a "0" when there's no data in this cell \$AI2, in this case.

If \$AS\$2="No" is TRUE it works perfect. No "0" just a blank.

I need to focus on the VLOOKUP portion: ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE)

And when there's NO value in \$AI2, have the result of the formula a BLANK.

_ParameterTable,19 is always a number. It's a "1" when it doesn't add a weight %, and when I need the customer tables to be increased it's whatever factor I need.

Does that make more sense?

Sorry, I meant is AI2 a number? Check also column 19 of the ParameterTable to see if it is numeric and if there are any VALUE errors in there.

Somehow this part \$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE) is not resulting as a number that the ROUND function can use.

Can you post a sample workbook showing the problem?

• Without see the workbook, it's difficult to fully understand the problem...

in the meantime, does this help?
=IF(\$AS\$2="No","",IFERROR(ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable,19,FALSE),0),""))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• That still adds a zero when there's no data in the refering cell. Here's one of the sheets we pulled out. The column in question is in yellow highlight.

Notice the formula I use now doesn't add a zero when referring cell is blank but I'm trying to check a different cell (\$AS\$2) to kick in the calculation. • You don't show the "Config" sheet that your formula is referencing!

If AS2 is resulting in #N/A! then there is no match found. The IFERROR() part of my formula should return a blank in that case.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• I'm so sorry I wasn't more careful with the uploaded file!

It was missing a sheet, therefore also missing _ParameterTable (named range)

Cell AS2 isn't an issue. It will ALWAYS be either "Yes" or "No".

My issue, as you can now see because I added the new formulas into the yellow highlight column on the G2-8 sheet, is that I want to use Cell AS2 as the KEY to decide whether to display values rather than using the W column same row (i.e. W2, W3, etc.) used in the old formulas.

As you can see though, when the \$AI column value is blank, instead of displaying nothing (blank) in column X, it displays "0". You don't show the "Config" sheet that your formula is referencing!

If AS2 is resulting in #N/A! then there is no match found. The IFERROR() part of my formula should return a blank in that case.

• Perhaps =IF(OR(\$AS\$2="No",\$AI2=""),"",ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable[],19,0),0))

Using XL2003

• Thank you, thank you, thank you!!!

PERFECT!

I was just going to ask what the [] was after the _ParameterTable and I noticed my version of Excel (2010) removed them. It does EXACTLY what I needed!...No "0" when the corresponding \$AI cell is blank!

Perhaps =IF(OR(\$AS\$2="No",\$AI2=""),"",ROUND(\$AI2*VLOOKUP(\$AJ\$2,_ParameterTable[],19,0),0))

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!