Why do Index and Match do not work when you've multiple numbers in Cell

[nsa1]

Hello,

I am trying to figure out if Index and Match function can work for indexing numbers separated by "," to be assign..

See the snapshot below

Thanks,
NA

 

[nsa1]

Try the following UDF:

Function IndexWithComma(rng As Range, cell As String)
  Dim c As Range
  Dim v As Variant, n As Variant
  Dim i As Long
 
  For Each c In rng.Columns(2).Cells
    v = Split(Replace(c.Cells(1).Value, Chr(10), ","), ",")
    n = Split(c.Offset(, -1).Cells(1).Value, ",")
    If UBound(v) = UBound(n) Then
      For i = 0 To UBound(v)
        If LCase(v(i)) = LCase(cell) Then
          IndexWithComma = n(i)
          Exit Function
        End If
      Next
    End If
  Next
  IndexWithComma = ""
End Function

HOW TO INSTALL UDFs (User Defined Functions)

If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IndexWithComma just like it was a built-in Excel function.​

​

For example:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Number	Variable		Variable	A	Y	m	hello	bye	z
2	1,2,3	X Y Z		Value	4	2		6	7	3
3	4	A
4	5	B
5	6,7	hello bye
6
7
8
9
10
Hoja3

Cell Formulas
Range	Formula
E2:J2	E2	=IndexWithComma($A$2:$B$10,E1)

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Thanks Dante! I ALT+F11 don't pop up anything? Do I click on Developer and click on Visual Basic?

 

[DanteAmor]

Do I click on Developer and click on Visual Basic?

Yes please.

Then:
once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IndexWithComma just like it was a built-in Excel function.

 

[nsa1]

Yes please.

Then:
once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use IndexWithComma just like it was a built-in Excel function.

Thanks. I added that code and saved it. Then went to my Sheet but saw no difference?

 

[DanteAmor]

We start from the beginning.

Please follow the steps below:

1. Click on Developer
2. Click on Visual Basic
3. In the VBA menu select the Insert option and then the Module option

4. In new panel, you paste the code.

Function IndexWithComma(rng As Range, cell As String)
  Dim c As Range
  Dim v As Variant, n As Variant
  Dim i As Long
 
  For Each c In rng.Columns(2).Cells
    v = Split(Replace(c.Cells(1).Value, Chr(10), ","), ",")
    n = Split(c.Offset(, -1).Cells(1).Value, ",")
    If UBound(v) = UBound(n) Then
      For i = 0 To UBound(v)
        If LCase(v(i)) = LCase(cell) Then
          IndexWithComma = n(i)
          Exit Function
        End If
      Next
    End If
  Next
  IndexWithComma = ""
End Function

5. You return to excel and in cell E2 you delete the formula you have and put this formula:

=IndexWithComma($A$2:$B$10,E1)

6. Copy the formula to the right to cell G2


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[nsa1]

We start from the beginning.

Please follow the steps below:

1. Click on Developer
2. Click on Visual Basic
3. In the VBA menu select the Insert option and then the Module option

View attachment 96955​

4. In new panel, you paste the code.

Function IndexWithComma(rng As Range, cell As String)
  Dim c As Range
  Dim v As Variant, n As Variant
  Dim i As Long
 
  For Each c In rng.Columns(2).Cells
    v = Split(Replace(c.Cells(1).Value, Chr(10), ","), ",")
    n = Split(c.Offset(, -1).Cells(1).Value, ",")
    If UBound(v) = UBound(n) Then
      For i = 0 To UBound(v)
        If LCase(v(i)) = LCase(cell) Then
          IndexWithComma = n(i)
          Exit Function
        End If
      Next
    End If
  Next
  IndexWithComma = ""
End Function

5. You return to excel and in cell E2 you delete the formula you have and put this formula:

=IndexWithComma($A$2:$B$10,E1)

6. Copy the formula to the right to cell G2


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

Thanks Dante for your patience. I am doing this VBA thing for the first time. So, appreciate all your help and patience. I added the code in the right place but didn't have .. =IndexWithComma($A$2:$B$10, G1). Well this formula gives me #NAME? fault?

 

[DanteAmor]

You're not adding the code in the right place, that's why it's giving you the "Name" error.

Start again with the steps.
You must put the code in a Module.

 

[nsa1]

You're not adding the code in the right place, that's why it's giving you the "Name" error.

Start again with the steps.
You must put the code in a Module.

Ok. I believe its in the correct location now...but still an error

 

[DanteAmor]

Ok, now the code is in the right place in Module4.

But now, what does the error message say?

You could show both images the cell with the formula.

Or simply copy the following onto a clean sheet:

varios 10ago2023.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Number	Variable		Variable	A	Y	m	hello	bye	z
2	1,2,3	X Y Z		Value	4	2		6	7	3
3	4	A
4	5	B
5	6,7	hello bye
Hoja1

Cell Formulas
Range	Formula
E2:J2	E2	=IndexWithComma($A$2:$B$10,E1)

To copy, click on the icon and paste on a clean sheet

 

[DanteAmor]

I can see that now you have 4 modules, if you added the code in more than one module that will cause another type of error.
Double click on each module, if you see the code then delete it, only the code should remain in one module.
You return to excel and put the formula.
That must work.

 

[nsa1]

Ok, now the code is in the right place in Module4.

But now, what does the error message say?

You could show both images the cell with the formula.

Or simply copy the following onto a clean sheet:

varios 10ago2023.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Number	Variable		Variable	A	Y	m	hello	bye	z
2	1,2,3	X Y Z		Value	4	2		6	7	3
3	4	A
4	5	B
5	6,7	hello bye
Hoja1

Cell Formulas
Range	Formula
E2:J2	E2	=IndexWithComma($A$2:$B$10,E1)

To copy, click on the icon and paste on a clean sheet
View attachment 96964

Same Error in a new clean sheet