Vlookup formula

[ddwonch]

This is the current formula I have. I would like it to continue to say (if N/A then Captial). I have some N/A showing in my rows.

Here is my formula =VLOOKUP($AO3,SLT!$A:$D,4,TRUE)

Here is the column that says N/A
<TABLE style="WIDTH: 82pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=109 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 82pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=109 height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Facilities Eng</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=middle height=20>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Admin</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Admin</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=middle height=20>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Tech Ops</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR></TBODY></TABLE

Denielle

 

[Desu Nota from Columbus]

=IF(ISERROR(VLOOKUP($AO3,SLT!$A:$D,4,TRUE)),"Capitol",VLOOKUP($AO3,SLT!$A:$D,4,TRUE))

 

[ddwonch]

Thank you so much. It worked.

DD

 

[Aladin Akyurek]

Is SLT!$A:$D sorted in ascending order on its first column?

 

[T. Valko]

This is the current formula I have. I would like it to continue to say (if N/A then Captial). I have some N/A showing in my rows.

Here is my formula =VLOOKUP($AO3,SLT!$A:$D,4,TRUE)

Here is the column that says N/A
<TABLE style="WIDTH: 82pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=109 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 82pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=109 height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Facilities Eng</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=middle height=20>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Admin</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Admin</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=middle height=20>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Tech Ops</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl110 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Manufacturing</TD></TR></TBODY></TABLE

Denielle

If you're using Excel 2007 or later...

=IFERROR(VLOOKUP($AO3,SLT!$A:$D,4),"Capitol")

Note that IFERROR will trap ALL errors, not just #N/A.