#### sezuh

##### Well-known Member
Hi
I using lookup formula below to get result if column (C) has three digit array,what would be the lookup formula if column (C) have 10 digit array and to get result like in D11? Any help would be much appreciated,thank you.
=VLOOKUP(LEFT(C2,2),\$A\$2:\$B15,2,FALSE)&","&VLOOKUP(MID(C2,4,2),\$A\$2:\$B15,2,FALSE)&","&VLOOKUP(RIGHT(C2,2),\$A\$2:\$B\$15,2,FALSE)
Code:
``````[TABLE="width: 551"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]01[/TD]
[TD="align: right"]11[/TD]
[TD]01 02 03[/TD]
[TD]11,14,26[/TD]
[/TR]
[TR]
[TD]02[/TD]
[TD="align: right"]14[/TD]
[TD]01 02 04[/TD]
[TD]11,14,29[/TD]
[/TR]
[TR]
[TD]03[/TD]
[TD="align: right"]26[/TD]
[TD]01 05 09[/TD]
[TD]11,32,47[/TD]
[/TR]
[TR]
[TD]04[/TD]
[TD="align: right"]29[/TD]
[TD]01 06 13[/TD]
[TD]11,35,63[/TD]
[/TR]
[TR]
[TD]05[/TD]
[TD="align: right"]32[/TD]
[TD]02 05 08[/TD]
[TD]14,32,44[/TD]
[/TR]
[TR]
[TD]06[/TD]
[TD="align: right"]35[/TD]
[TD]03 06 14[/TD]
[TD]26,35,72[/TD]
[/TR]
[TR]
[TD]07[/TD]
[TD="align: right"]38[/TD]
[TD]05 09 11[/TD]
[TD]32,47,56[/TD]
[/TR]
[TR]
[TD]08[/TD]
[TD="align: right"]44[/TD]
[TD]06 09 14[/TD]
[TD]35,47,72[/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD="align: right"]47[/TD]
[TD]07 08 12[/TD]
[TD]38,44,60[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]51[/TD]
[TD]03 05 10[/TD]
[TD]26,32,51[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD="align: right"]56[/TD]
[TD]01 02 03 04 05 06 07 08 09 10[/TD]
[TD]11 14 26 29 32 35 38 44 47 51[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: right"]60[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD="align: right"]63[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"]72[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]``````

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

I think you'll need some VBA for this.

Try this UDF
Code:
``````Public Function DigitArray(c As Range) As String
Dim RsltArr As Variant, MyArray As Variant, MyString As String, i As Long
RsltArr = Array(11, 14, 26, 29, 32, 35, 38, 44, 47, 51, 56, 60, 63, 72)
MyArray = Split(c, " ")
MyString = RsltArr(Val(MyArray(0)) - 1)
For i = LBound(MyArray) + 1 To UBound(MyArray)
MyString = MyString & "," & RsltArr(Val(MyArray(i)) - 1)
Next i
DigitArray = MyString
End Function``````

Then use it in a cell like
=DigitArray(C2)

Hi Jonmo1,
Thanks ever so much for your code ,it works perfect. But if I may ask;do I need the number in column A and B?,in the code the array there are 14 numbers when I increased to 20 and if there are
Any digit more than 15 in column C it gives me an error of (#VALUES) any thought on that please?
Here an example;
Code:
``````[TABLE="width: 656"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]01 02 03 04 05 06 07 08 09 10 11 12 13 14[/TD]
[TD]11,14,26,29,32,35,38,44,47,51,56,60,63,72[/TD]
[/TR]
[TR]
[TD]01 02 03 04 05 06 07 08 09 10 11 12 15 16[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]``````
Much obliged.

The code I posted bypassed what you had in columns A and B.

For digits above 14 you'd just add to this part here
RsltArr = Array(11, 14, 26, 29, 32, 35, 38, 44, 47, 51, 56, 60, 63, 72, resultfor15, ressultfor16, etc..)

If you wanted to keep those values in a range of cells, you could change it this way
Code:
``````Public Function DigitArray(c As Range, RsltArr As Range) As String
Dim MyArray As Variant, MyString As String, i As Long
MyArray = Split(c, " ")
MyString = RsltArr(Val(MyArray(0)))
For i = LBound(MyArray) + 1 To UBound(MyArray)
MyString = MyString & "," & RsltArr(Val(MyArray(i)))
Next i
DigitArray = MyString
End Function``````

Then use the formula like this
=digitarray(C2,\$B\$2:\$B\$20)

B2:B20 is the converted value for each number (what you had in column B)

Jonmo1,
Thanks very .........very much for your invaluable help,with your second code solved all my problems.
Very much obliged.

You're welcome

Replies
2
Views
280
Replies
6
Views
197
Replies
1
Views
111
Replies
5
Views
166
Replies
6
Views
201

1,203,236
Messages
6,054,298
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back