Hi,
I am trying to combine the Vlookup, Left and Right function in one cell.
Column F3:F12 contain of number and G3:G12 is type and Column A is a lookup value for last four characters (A3:A14).
I am apply the following formula in C3="K"&LEFT(VLOOKUP(RIGHT(--$A3,4)&"*",$F$3:$G$12,2,0),2) based on the below
data but I am getting error.
Any help would be appreciated
[TABLE="width: 540"]
<colgroup><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]DESIRED RESULT
A B C [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]DATA
F G
[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]RESULT[/TD]
[TD]FORMULA REQUIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]3187[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003187[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3196[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003196[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3135[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003135[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003206[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100002853[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3208[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003208[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3206[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003139[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3139[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003154[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3154[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003159[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3159[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003213[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3199[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3133[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am trying to combine the Vlookup, Left and Right function in one cell.
Column F3:F12 contain of number and G3:G12 is type and Column A is a lookup value for last four characters (A3:A14).
I am apply the following formula in C3="K"&LEFT(VLOOKUP(RIGHT(--$A3,4)&"*",$F$3:$G$12,2,0),2) based on the below
data but I am getting error.
Any help would be appreciated
[TABLE="width: 540"]
<colgroup><col span="2"><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]DESIRED RESULT
A B C [/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]DATA
F G
[/TD]
[/TR]
[TR]
[TD]CODE[/TD]
[TD]RESULT[/TD]
[TD]FORMULA REQUIRED[/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]3187[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003187[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3196[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003196[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3135[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003135[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003206[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]NONE[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100002853[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3208[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003208[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3206[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003139[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3139[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003154[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3154[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003159[/TD]
[TD]18KPY[/TD]
[/TR]
[TR]
[TD]3159[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]100003213[/TD]
[TD]16KPY[/TD]
[/TR]
[TR]
[TD]3199[/TD]
[TD]K18[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3133[/TD]
[TD]K16[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]