Vlookup help

[howard]

I have used the following Vlookup formula to look up a description

=IF(ISTEXT(B1096),VLOOKUP(B1096+0,TBTXT!B:E,2,FALSE),VLOOKUP(B1096,TBTXT!B:E,2,FALSE))

This formula works well if the value in B1096 is say 199992 . However, if an X or K appears first for ex X10196, then I get #value!

I have attached some sample data. Your assistance in resolving this is most appreciated

Excel Workbook
	B	C
1095	199940	Dividends Paid
1096	199992	Transfer To Balance Sheet
1097	X13040	#VALUE!
1098	X13060	#VALUE!
1099	X13080	#VALUE!
1100	X13100	#VALUE!
TB Consolidation

 

[technicalupload]

Could you also share "TBTXT" sheet, I will re-create the Excel formula accordingly. Thanks!

 

[Firefly2012]

Hi

How about:

=VLOOKUP(IF(ISNUMBER(B1096+0),B1096+0,B1096),TBTXT!B:E,2,FALSE)

 

[howard]

Thanks for the help, much appreciated

 

[howard]

Thanks for the reply

Some sample data from "TBTXT"

Excel Workbook
	B	C
1174	199916	Coy Tax - Deferred
1175	199930	Goodwill W/off
1176	199940	Dividends Paid
1177	199992	Transfer To Balance Sheet
1178	X11300	New Vehicle Stock Units
1179	X11302	N/V Inv Units 0-30
1180	X11304	N/V Inv Units 31-60 Days
1181	X11306	N/V Inv Units 61-90
1182	X11308	N/V Inv Units >90days
1183	X11320	N/V Inv Value 0-30
1184	X11322	N/V Inv Value 31-60
1185	X11324	N/V Inv Value 61-90
1186	X11326	N/V Inv Value >90
TBTXT

 

[Firefly2012]

Hi

How about:

=VLOOKUP(IF(ISNUMBER(B1096+0),B1096+0,B1096),TBTXT!B:E,2,FALSE)

Did my suggestion work for you?

 

[howard]

Hi Fireply2012

Thanks, Your suggestion worked 100% for me

 

[Firefly2012]

Excellent! Thanks for letting me know