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

 

[Fluff]

Can you paste your actual formula.

 

[nsa1]

Can you paste your actual formula.

=IF(ISNA(MATCH(E1,B2:B4,0)),"",INDEX(A2:B4,MATCH(E1,B2:B4,0),1))

 

[Fluff]

Thanks for that.
How about

=IF(ISNA(MATCH("*"&E1&"*",B2:B4,0)),"",INDEX(A2:B4,MATCH("*"&E1&"*",B2:B4,0),1))

 

[nsa1]

Thanks for that.
How about

=IF(ISNA(MATCH("*"&E1&"*",B2:B4,0)),"",INDEX(A2:B4,MATCH("*"&E1&"*",B2:B4,0),1))

It gives me 4 as answer... and changes input field from X to B?

 

[Fluff]

It gives me 4 as answer

Which is correct because you are matching E1.

and changes input field from X to B

A formula in F2 cannot change what is in F1

 

[nsa1]

Oops Sorry.. I tried it but it gives me 1,2,3. I want X = 1, Y =2 and Z=3...is that possible?

 

[Fluff]

I tried it but it gives me 1,2,3

That's exactly what it should do.

 

[nsa1]

I understand but I am not looking foe "1,2,3".

I have a situation in my spreadsheet where if I come across a cell with "X Y Z" and it's Index match is "1,2,3" then I want to have X = 1, Y =2 and Z = 3....not printing "1,2,3" for all X, Y and Z?

 

[DanteAmor]

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