P
PMC1
Hi,
Using worksheet named "Sheet1", to convert text which looks like this -
"1001001 Sundry Expenses" into this "1001001" I'm using the following
formula:
=IF(B17="","",(IF(TYPE(VALUE(LEFT(TRIM(B17),1)))=1,LEFT(TRIM(B17),FIND("
",TRIM(B17),1)),TRIM(B17))))
The original text is in Cell Bx and the result is in Ax
In other worksheets I'm using Vlookups against the contents of column A
to return the relevant columnar results.
My problem is in the Vlookup formula if I enter something like
=Vlookup("1001001",Sheet1!$A$Z,16,0) I get an #N/A. It seems I need to
use the above trim formula on the sheet I'm performing the VLookup then
use a Cell reference in order for the result to be returned i.e. Place
the full account code and Desc (e.g. 1001001 Sundry Expences) somewhen
in the new worksheet then use the formula mentioned above to trim this
down to 1001001 then use the cell reference of the result in the
VLookup (=Vlookup(C22,Sheet1!$A$Z,16,0)).
How can I get Excel VLookup to recognise when I manually input a number
(like "1001001") and to return the result if this number appears in Row
A.
Any help appreciated
...pc
Using worksheet named "Sheet1", to convert text which looks like this -
"1001001 Sundry Expenses" into this "1001001" I'm using the following
formula:
=IF(B17="","",(IF(TYPE(VALUE(LEFT(TRIM(B17),1)))=1,LEFT(TRIM(B17),FIND("
",TRIM(B17),1)),TRIM(B17))))
The original text is in Cell Bx and the result is in Ax
In other worksheets I'm using Vlookups against the contents of column A
to return the relevant columnar results.
My problem is in the Vlookup formula if I enter something like
=Vlookup("1001001",Sheet1!$A$Z,16,0) I get an #N/A. It seems I need to
use the above trim formula on the sheet I'm performing the VLookup then
use a Cell reference in order for the result to be returned i.e. Place
the full account code and Desc (e.g. 1001001 Sundry Expences) somewhen
in the new worksheet then use the formula mentioned above to trim this
down to 1001001 then use the cell reference of the result in the
VLookup (=Vlookup(C22,Sheet1!$A$Z,16,0)).
How can I get Excel VLookup to recognise when I manually input a number
(like "1001001") and to return the result if this number appears in Row
A.
Any help appreciated
...pc