Lookup Formula Help Please.?

sezuh

Well-known Member
Joined
Nov 19, 2010
Messages
708
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)
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
Jonmo1,
Thanks very .........very much for your invaluable help,with your second code solved all my problems.:)
Very much obliged. (y)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top