ISNA/copy a vlookup

[Hoosier03]

I am using excel 2007. I want my cells to have a dash(-) in them if the vlookup does not return anything instead of N/A. This is my equation


=IF(ISNA(VLOOKUP($D$364,'AOP Query'!$C$22:$DI$440,60,FALSE)),"-",VLOOKUP($D$364,'AOP Query'!$C$22:$DI$440,60,FALSE))


The problem is it is returning a dash for every cell not just the ones that have no value. How do I fix this?


My other question is there a way to drag a vlookup across rows. I would like to be able to drag the vlookup across the cells and have the 60 change with every row, but when I drag the equation across the 60 stays constant.

Thanks,

Rodney

 

[Excelestial]

the answer to 1st part of question: if a dash is returning for every cell, then whatever is in your D364 isn't matching in the left most column of your array...therefore is yielding a #NA.

Part 2:
Insert a help column to use as a counter (Column A, typically). Begin the first cell (perhaps A22) with the number 60 and number them down in ascending order...61, 62, 63...etc. And now use your formula to reference the help column:

=IF(ISNA(VLOOKUP($D$364,'AOP Query'!$C$22:$DI$440,$A22,FALSE)),"-",VLOOKUP($D$364,'AOP Query'!$C$22:$DI$440,$A22,FALSE))

 

[Hoosier03]

I guess I did not word my first question right. Here is the column I referenced.

<TABLE style="WIDTH: 65pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=86 border=0><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><TBODY><TR style="HEIGHT: 18pt" height=24><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18pt; BACKGROUND-COLOR: transparent" align=middle width=86 height=24>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>482</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>15.9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=middle height=17>#N/A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl540 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>-10</TD></TR></TBODY></TABLE>

The cells with numbers are correct. I need the ones with N/A to return a blank or a zero so I can sum the numbers. The ISNA function I wrote made every cell return a dash instead of just the ones without any values.

 

[Hoosier03]

For the second part my table begins in column C. The first row of data is in column BJ. Which one should I reference?


Thanks,

Rodney

 

[Hoosier03]

I got the ISNA function to work.


Thanks.


Rodney

 

[FKoenig]

For the second part my table begins in column C. The first row of data is in column BJ. Which one should I reference?


Thanks,

Rodney

Are you interchanging "Row" with "Column" in your vocabulary?

"The first row of data is in column BJ" doesn't seem to make sense.

 

[Aladin Akyurek]

For the second part my table begins in column C. The first row of data is in column BJ. Which one should I reference?


Thanks,

Rodney

Which cell do you enter the first formula instance?

 

[Hoosier03]

The first row of data is in column BJ


I mean column sorry.

 

[Hoosier03]

This is the first equation


=IF(ISNA(VLOOKUP(D364,'AOP Query'!$C$22:$DI$440,60,FALSE)),"0",VLOOKUP(D364,'AOP Query'!$C$22:$DI$440,60,FALSE))



60 is in column BJ. The table starts in column C.

 

[Aladin Akyurek]

This is the first equation


=IF(ISNA(VLOOKUP(D364,'AOP Query'!$C$22:$DI$440,60,FALSE)),"0",VLOOKUP(D364,'AOP Query'!$C$22:$DI$440,60,FALSE))



60 is in column BJ. The table starts in column C.

In which cell do you enter this formula, in A1 maybe?