Formula where some account numbers are numbers and some are text

[howard]

I have account numbers in col B, where the vast majority are text shown as ' before number for eg '1101. However where the number does not appear as text then the formula returns "Missing" even though it apears on sheet TBTXT


I have the following formula in Col D, which works perfectly where the account number in Col B is shown as text, but as soon as the number is a proper number ending in an X eg 7956X, it shows as "missing", even though the number ending in an X appears on sheet TBTXT

=IF(ISERROR(VLOOKUP($B2+0,TBTXT!$B$2:$E$1350,4,FALSE)),"MISSING",VLOOKUP($B2+0,TBTXT!$B$2:$E$1350,4,FALSE))

Your assistance in resolviing this is most appreciated

Excel Workbook
	A	B	C	D
1211	7783	187265	Demo Fuel F&I	12,992.07
1212	7784	187275	Demo Mtce F&I	0.00
1213	G050	187328	Fines F&I	0.00
1214	6904	199016	Int Paid Other2	0.00
1215	6940	199930	Goodwill W/off	0.00
1216	614X	X16215	SVC No. Of Mtc Jobs	MISSING
1217	650X	X16320	SVC Days Pd Lve Per tech	MISSING
TB Consolidation

 

[Aladin Akyurek]

What is your version of Excel?

 

[howard]

Hi Aladin

I'm using Excel 2010

 

[Aladin Akyurek]

Hi Aladin

I'm using Excel 2010

Try...

=IFERROR(VLOOKUP(IF(ISNUMBER($B2+0),$B2+0,$B2),TBTXT!$B$2:$E$1350,4,0),"MISSING")

 

[howard]

Hi Aladin

Thanks for the help, much appreciated

Regards

Howard

 

[Aladin Akyurek]

Hi Aladin

Thanks for the help, much appreciated

Regards

Howard

You are welcome. Thanks for providing feedback.