if and vlookup error

[marco10]

=IF(C79="","",VLOOKUP(C79,$C$505:$CN$556,90,FALSE)) ---
--- this is the actual formula and i had to make some change in the sheet and as i added some columns;

it changed to =IF(C79="","",VLOOKUP(C79,$C$505:$CR$556,90,FALSE)) but only gives me a ( - ) as a result,

so i tried changing it to--- =IF(C79="","",VLOOKUP(C79,$C$505:$CR$556,96,FALSE))

however, for some reason it is giving me #ref! error
would be grateful if anyone could explain my mistake.
thank you

 

[joeu2004]

96 should be 94 or less. That parameter is the relative column in the lookup range C:CR from which you want to return a value. CR is column 94 relative to C, which is column 1 relative to the range.

That should fix the #REF error.

You don't provide sufficient information for us to explain the result that you write ``( - )``.

First, are the parentheses part of the result? Or do you mean that the cell displays a dash (aka hyphen)?

If the latter, look at the cell format as a Custom format: select the cell, right-click and click Format Cells, Number tab, Custom.

You might see something like the following:

_(#,##0.00_);_((#,##0.00);_("-"??_);_(@_)

The third field (highlighted) causes Excel to display a dash when the cell value is exactly zero.

If that is the case, VLOOKUP might be returning exact zero. (Or of course, it might be returning the dash as a text value. What does ISNUMBER(cellref) return?)

Why? Again, you do not provide sufficient information for us to help.

Perhaps returning the value from column CR instead of colum CN (90 relative to C:CR) will fix that problem, and the issue becomes moot.

 

[marco10]

@joeu2004 thank you so much for your help, the issue has already been resolved; thanks to you and @Dave87. Yes, it was a hyphen, probably because of no value or zero in that 90 column as i added more columns and indeed once i changed it to 94 the values appeared and the issue was solved.