PDA

View Full Version : Solved: if & vlookup formula : help requried



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

kevkni
07-23-2012, 08:20 AM
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
07-23-2012, 04:08 PM
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
07-24-2012, 03:35 AM
Hi

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

thank you both of you.

Regards