Solved: if & vlookup formula : help requried

[satish gubbi]

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

[kevkni]

Hi,

For your formula, try

=IF(LEN(D1>1),VLOOKUP(LEFT(D1,1),$A$1:$B$10,2,FALSE)&" and "
& (VLOOKUP(MID(D1,1,1),$A$1:$B$10,2,FALSE)),(VLOOKUP(LEFT(F1,1),$A$1:$B$10,2 ,FALSE)))
In your original formula, the MID function was looking for the second character in a cell which only contained one character.

Hopefully this should work

Kevin

[Bob Phillips]

Try this

=VLOOKUP(LEFT(D1,1),$A$1:$B$10,2,FALSE)&IF(LEN(D1)>1," and "&VLOOKUP(MID(D1,2,1),$A$1:$B$10,2,FALSE),"")

[satish gubbi]

Hi

thank you very much for both of you, formula provided by xld is working as expected

thank you both of you.

Regards