VLOOKUP does not work when lookup #N/A

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
Hi,

I am trying to lookup values in one table based on a lookup table using Vlookup.

All works fine. However, the vlookup which is suppose to lookup the value of #N/A and return as 0 seems to return #N/A.

Example- In table 1, the vlookup in cell C3 suppose to return as 0 based on the lookup table. However, it returns as #N/A.

Excel Workbook
BCDEFGH
1Table 1***Lookup Table**
2StatusScore**StatusScore*
3#N/A#N/A**#N/A0*
4****00*
5****R110*
6****R220*
7****R330*
8****QA140*
9****QA250*
10****QA360*
11****QA470*
12****QR180*
13****QR290*
14****QR3100*
15*******
Sheet1





Is there a way to get it to return as 0 per the lookup table ?

Thanks.
 
Last edited:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Ed in Aus

Well-known Member
Joined
Jul 24, 2007
Messages
829
Is there a way to get it to return as 0 per the lookup table ?

Yes, remove the #NA from you table and replace it with another value, even typing in a ' at the start of the cell will get this to work i.e. '#NA instead of #NA.

No idea why but sureley someone here will know why that is.
 

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
Hi Ed,

I modified the vlookup as follows. However, when the value in cell F9 in table 1 is 0%, the vlookup returns as #N/A in cell G9. The source data for cell F9 is empty. Therefore, instead of returning #N/A, I have modified the formula to return as 0%. However, it returns as #N/A even though it should return as 0 based on the lookup table. Please help.




Excel Workbook
FG
99Lookup Table*
100Status CalculationScore
1010%0
102R110
103R220
104R330
105QA140
106QA250
107QA360
108QA470
109QR180
110QR290
111QR3100
Summary
 

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
<TABLE style="WIDTH: 651pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=868><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 315pt; mso-width-source: userset; mso-width-alt: 15360" width=420><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl67 height=20 width=64>*

</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>B</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 315pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=420>C</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>D</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>E</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>F</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl68 width=64>G</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl72 width=64>H</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=35 width=64>1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Table 1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=420>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>Lookup Table</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Status</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl75 width=420>Score</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>Status</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>Score</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 33pt" height=44><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 33pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=44 width=64>3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64 align=middle>#N/A</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420> =IF(ISNA(VLOOKUP(B4,$F$4:$G$16,2,FALSE)),0,VLOOKUP(B4,$F$4:$G$16,2,FALSE))</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64 align=middle>#N/A</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64 align=middle>#N/A</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>0%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>0</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>5</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>R1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>10</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>6</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>R2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>20</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>7</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>R3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>30</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>8</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QA1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>40</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>9</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QA2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>50</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>10</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QA3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>60</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>11</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QA4</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>70</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>12</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QR1</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>80</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>13</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QR2</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>90</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl69 height=20 width=64>14</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl76 width=420>1000%</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 width=64>*</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>QR3</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl65 width=64>100</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl73 width=64>*</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #cacaca; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl70 height=21 width=64>15</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=64>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: #fcd5b4; WIDTH: 315pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl77 width=420>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=64>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=64>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=64>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=64>*</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: black; BORDER-RIGHT: #3867a6 1pt solid" class=xl74 width=64>*

</TD></TR></TBODY></TABLE>
 

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
It appears your vlookup is using the same leftmost column. If so, and NA will be returned on both lookups so you'll therefore need the ISNA for both columns F and G to trap it.
 

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
Excelestial,

Thank you for your solution. It worked.

Appreciate your time and patience.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top