Return nth term from comma delimited cell base on value of adjacent comma delimited cell.

[tds019]

Hoping someone can help me with a function. I want to return the number in the left column that corresponds to “99292” in the middle column. I know I can use the “find” function to find the position of 99292 in the middle column, but that doesn’t correspond to anything meaningful in the left column because the number of digits in the middle column will always vary.

I know I could also turn commas into columns and it should be easy from there but I want to do this in only a few columns since the number of values could get very large making comma to columns unwieldy.

In the right column below I have explained what value the function should return.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1, 1, -1, 1, 1, 1, 4, 1, -1, 1, -1, 1[/TD]
[TD]2500003, 2500003, 2500003, 27100000, 99143, 99291, 99292, 2500003, 2500003, 99143, 99143, 96372[/TD]
[TD]Here I would want to return “4” since that is the number in the left column that corresponds to 99292 in the right column, they’re both the seventh number[/TD]
[/TR]
[TR]
[TD]1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 7, 1, -1[/TD]
[TD]73130, 73110, 73110, 36415, 80048, 85730, 85027, 85610, 86900, 86901, 86850, 86900, 86900, 36415, 36415, 27200000, 99285, 36592, 94761, 99291, 99292, 12001, 99285[/TD]
[TD]Here I would want to return 7. 99292 is the third to last number in the right column so I would want to return the third to last number in the left column.[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 613"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

Here is one option you can try, albeit a little unwieldy:

Excel 2013
	A	B	C
1	1, 1, -1, 1, 1, 1, 4, 1, -1, 1, -1, 1	2500003, 2500003, 2500003, 27100000, 99143, 99291, 99292, 2500003, 2500003, 99143, 99143, 96372	4
2	1, 1, -1, 1, 1, 1, 1, 1, 1, 1, 1, -1, 1, -1, 1, 1, 1, 1, 1, 1, 7, 1, -1	73130, 73110, 73110, 36415, 80048, 85730, 85027, 85610, 86900, 86901, 86850, 86900, 86900, 36415, 36415, 27200000, 99285, 36592, 94761, 99291, 99292, 12001, 99285	7
Sheet1

Cell Formulas
Range	Formula
C1	=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(1+LEN(LEFT(B1,FIND(", 99292,",", "&B1&",")))-LEN(SUBSTITUTE(LEFT(B1,FIND(", 99292,",", "&B1&",")),",","")))*99-98,99))

 

[tds019]

Wow! That seems to work! Thank you so much. A few follow-up questions/comments.

1. Some do not contain 99292 at all in which case I want the column to return 0. I was able to do this with a modification to your function(my data is actually in H and I):
=IF(ISNUMBER(SEARCH(99292,I1227)),(TRIM(MID(SUBSTITUTE(H1227,",",REPT(" ",99)),(1+LEN(LEFT(I1227,FIND(", 99292,",", "&I1227&",")))-LEN(SUBSTITUTE(LEFT(I1227,FIND(", 99292,",", "&I1227&",")),",","")))*99-98,99))),0)

2. Is there a limit to how many terms this will work for? Some are returning #VALUE!. It seems to be the very long ones. Unfortunately some have around 5,000 terms, I just pasted some of the simpler ones to this thread. Is there any way to modify the formula to make it work for cells that have 5 to 6 thousand terms?

 

[eke_rula]

Wow! That seems to work! Thank you so much. A few follow-up questions/comments.

1. Some do not contain 99292 at all in which case I want the column to return 0. I was able to do this with a modification to your function(my data is actually in H and I):
=IF(ISNUMBER(SEARCH(99292,I1227)),(TRIM(MID(SUBSTITUTE(H1227,",",REPT(" ",99)),(1+LEN(LEFT(I1227,FIND(", 99292,",", "&I1227&",")))-LEN(SUBSTITUTE(LEFT(I1227,FIND(", 99292,",", "&I1227&",")),",","")))*99-98,99))),0)

2. Is there a limit to how many terms this will work for? Some are returning #VALUE!. It seems to be the very long ones. Unfortunately some have around 5,000 terms, I just pasted some of the simpler ones to this thread. Is there any way to modify the formula to make it work for cells that have 5 to 6 thousand terms?

You try this formula:

=IFERROR(LOOKUP(100,--MID(A1,FIND("X",SUBSTITUTE(A1,",","X",LEN(LEFT(B1,FIND(99292,B1)))-LEN(SUBSTITUTE(LEFT(B1,FIND(99292,B1)),",",""))))+1,ROW($1:$4))),)

 

[tds019]

That works! Perfectly! The only oddity is if there are more than one 99292 it returns the number associated with the first one, which is fine for my purposes.