satish gubbi
07-23-2012, 07:42 AM
Hi
I have below formula which expected to pick the correspoding values from given reference, this formula works if the cell value has two digits, if cell has single digit, its not working,
request your help in getting this formula working
Formulas is as below
=IF(LEN(D1>1),VLOOKUP(LEFT(D1,1),$A$1:$B$10,2,FALSE)&" and "
&(VLOOKUP(MID(D1,2,1),$A$1:$B$10,2,FALSE)),(VLOOKUP(LEFT(F1,1),$A$1:$B$10,2, FALSE)))
and other formula
=IF(LEN(G1<>1),VLOOKUP(LEFT(G1,1),$A$1:$B$10,2,FALSE)&" and "&(VLOOKUP(MID(G1,2,1),$A$1:$B$10,2,FALSE)),(VLOOKUP(LEFT(G1,1),$A$1:$B$10,2, FALSE)))
attached is the workbook for your reference
I have below formula which expected to pick the correspoding values from given reference, this formula works if the cell value has two digits, if cell has single digit, its not working,
request your help in getting this formula working
Formulas is as below
=IF(LEN(D1>1),VLOOKUP(LEFT(D1,1),$A$1:$B$10,2,FALSE)&" and "
&(VLOOKUP(MID(D1,2,1),$A$1:$B$10,2,FALSE)),(VLOOKUP(LEFT(F1,1),$A$1:$B$10,2, FALSE)))
and other formula
=IF(LEN(G1<>1),VLOOKUP(LEFT(G1,1),$A$1:$B$10,2,FALSE)&" and "&(VLOOKUP(MID(G1,2,1),$A$1:$B$10,2,FALSE)),(VLOOKUP(LEFT(G1,1),$A$1:$B$10,2, FALSE)))
attached is the workbook for your reference