If Formula

[jonc31]

If I have an account number in cell B3 but on a driver page I have a list of good account numbers how can I make the formual below say "IF" B3 = any of the account numbers in the range "Driver!D3:D5" put yes otherwise put nothing. The account number in B3 looks like this "054000024"

The list on the driver looks like this
<TABLE style="WIDTH: 57pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=76 border=0 x:str><COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 57pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=76 height=17><TABLE style="WIDTH: 165pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=220 border=0 x:str><COLGROUP><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 165pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" width=220 height=17> 05A000024</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17> 05A000211</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: yellow" height=17> 05A000229</TD></TR></TBODY></TABLE>


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


=IF(B3=Driver!D3:D5,Yes,"")


In the end this formula should return a Yes because the account number is part of the range. Please help

 

[JamesW]

Hi Jonc,

The Match returns a number relative to the row (for example, it may return 3 as it has found a match on the 3rd row of the range).

The IsNumber then checks to see if the match returned a number (row number), if it didn't then it will return #N/A - which isn't a number.