Vlookup brings back N/A

excelnewbie · May 11th 2003

How do I stop the N/A from displaying when doing a V lookup. If the value is N/A I just want a blank cell or better yet a 0. Thanks

**Jack in the UK** · May 12th 2003

Your foumla is in cell A14, change Jack to test and you shold get a zero as you requested.

Hope this helps

Jack in the UK

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Oz Stats may.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2002 XP : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"><U>F</U>ile <U>E</U>dit <U>V</U>iew <U>I</U>nsert <U>O</U>ptions <U>T</U>ools <U>D</U>ata <U>W</U>indow <U>H</U>elp <A onclick=show_popup(); href="#javascript:void(0)"><U>A</U>bout</A></TD><TD vAlign=center align=right><FORM name=formCb724366><INPUT onclick='window.clipboardData.setData("Text",document.formFb049879.sltNb288590.value);' type=button value="Copy Formula" name=btCb259793></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb049879><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288590.value" name=sltNb288590><OPTION value==VLOOKUP(D1,A1:B3,2,FALSE) selected>A6<OPTION value==IF(ISERROR(VLOOKUP(D9,A9:B11,2,FALSE)),0,(VLOOKUP(D9,A9:B11,2,FALSE)))>A14</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8><B>=</B></TD><TD align=left bgColor=white><INPUT size=80 value==VLOOKUP(D1,A1:B3,2,FALSE) name=txbFb195777></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><BR></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Ozgrid</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Yes 1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">TEST</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">excelnewbie</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Yes 2</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Jack</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Yes 3</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>6</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"><A onclick="document.formFb049879.sltNb288590.options[0].selected=true; document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288590.value;" href="#javascript:void(0);">#N/A</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>7</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>8</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>9</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Ozgrid</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Yes 1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Jack</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>10</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">excelnewbie</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Yes 2</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>11</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff">Jack</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffff00">Yes 3</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>12</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>13</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>14</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffff00"><A onclick="document.formFb049879.sltNb288590.options[1].selected=true; document.formFb049879.txbFb195777.value = document.formFb049879.sltNb288590.value;" href="#javascript:void(0);">Yes 3</FONT></A></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt solid; BACKGROUND-COLOR: #ffffff"> </TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=5><TABLE width="100%" align=left VALIGN="TOP"><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left><U>Sheet2</U></TD><TD> </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR><FONT color=#339966 size=1>[HtmlMaker light Ver1.10] </FONT><FONT color=#339966 size=1>To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT><BR><FONT color=red size=1>PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT></CENTER>

Egad · May 12th 2003

Instead of a nested Vlookup as used in Jack's example: =IF(ISERROR(VLOOKUP(D9,A9:B11,2,FALSE)),0,(VLOOKUP(D9,A9:B11,2,FALSE)))

...you could use a sumif instead.
=SUMIF(A1:A3,D1,B1:B3) (assuming your datarange is a1:b3 and 'test' is in d1).
This only works if you want to return numeric data.

Dave Hawley · May 12th 2003

Hi excelnewbie

I normally handle the #N/A! with a COUNTIF, eg

Code

=IF(COUNTIF($D$1:$D$600,"Cat"),VLOOKUP("Cat",$D$1:$G$600,2,FALSE),0)

excelnewbie · May 12th 2003

Thanks everyone, I will give these examples a try at work tomorrow.

Have a great night !

Will Riley · May 12th 2003

A more efficient option is:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LookupTable,0,1),0)),VLOOKUP(lookup-value,LookupTable,ColIdx,0),"")

Another, if you installed morefunc.xll (an add-in)...

=IF(ISNA(SETV(VLOOKUP(lookup-value,LTable,ColIdx,0))),"",GETV())

These options avoid computing the same formula twice, which is what will happen in the ISERROR solution(s)

HTH

excelnewbie · May 12th 2003

Thanks very much WillR. Where do you get the add-in? and is it free?

Dave Hawley · May 12th 2003

Hi excelnewbie

You can download it, and MUCH more from here: http://www.ozgrid.com/Services/ExternalFree.htm Or click Here to get straight to it.

Imbuzi · May 12th 2003

Another twist to Jack's formula

Jack's example: =IF(ISERROR(VLOOKUP(D9,A9:B11,2,FALSE)),0,(VLOOKUP(D9,A9:B11,2,FALSE)))

=IF(ISNA(VLOOKUP(D9,A9:B11,2,0)),0,VLOOKUP(D9,A9:B11,2,0))

Vlookup brings back N/A

Participate now!

Share

Users Viewing This Thread