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
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 |
Last edited: