I am wondering why this index formula is suddenly not working

[joshman108]

Workbook
https://drive.google.com/file/d/0B1TMLUGTVwb9RTdSVUM3aDNMNFE/view?usp=sharing


The formula being:
=IFERROR(INDEX($L$1:$L$5000,SMALL(IF(ISNUMBER(SEARCH("|"&$B2,"|"&$L$1:$L$5000)),
ROW($L$1:$L$5000)-ROW($L$1)+1),COLUMNS($E$2:E2))),"")

The idea is it searches each value in B starting at b2 against l1:l5000 and returns the first found instance to e2, then f2,g2,h2,i2,k2 as you drag right and e3 (etc) as you drag down. I have marked in yellow related values in b2 and l:l as they should be returned in e2,f2,g2.

Can someone help explain why this wouldn't be working?

This is part of a macro, but I have entered the formula in by hand to the same results. If you want you may click sheet 1, run the sub in module 3 to see where I started from and where I wound up. The top sub in module 2 is the completed process, but not important to this.

EDIT: After posting my brain always starts thinking differently, so I was able to determine that the value in b2 needs to be the first thing in a given string of L:L as opposed to just being present in the middle. How may we amend this formula to account for this?? Thanks again!

 

[StephenCrump]

What do you mean by not working? Your workbook looks like this:

E2: {=IFERROR(INDEX($L$1:$L$5000,SMALL(IF(ISNUMBER(SEARCH("|"&$B2,"|"&$L$1:$L$5000)), ROW($L$1:$L$5000)-ROW($L$1)+1),COLUMNS($E$2:E2))),"")}

Don't the results in E2:H2 match what you've described as wanting to see?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1		FIND			RESULTS							qw
2		ABC			ABC 1	ABC 2	ABC 3	ABC 4				qw
3												qw
4												ABC 1
5												qw
6												ABC 2
7												ABC 3
8												qw ABC
9												qw
10												ABC
11												qw
12												ABC 4
All Products Images_CPI

 

[StephenCrump]

I was able to determine that the value in b2 needs to be the first thing in a given string of L:L as opposed to just being present in the middle. How may we amend this formula to account for this?? Thanks again!

Sorry, I misinterpreted!

Just take out the two references to: "|"&

 

[FDibbins]

This is a sample I was working on for another member, you should be able to adapt it for your needs...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Title1 : 1 . 13[/td][td]133[/td][td][/td][td] : 1 . 13[/td][td]

133​

[/td][/tr]

[tr][td]

2​

[/td][td]Title1 : 1 . 13[/td][td]8763[/td][td][/td][td][/td][td]

8763​

[/td][/tr]

[tr][td]

3​

[/td][td]Title1 : 2 . 14[/td][td]124[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Title1 : 2 . 14[/td][td]8211[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Title2 : 1 . 15[/td][td]131[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Title2 : 2 . 16[/td][td]129[/td][td][/td][td][/td][td][/td][/tr]
[/table]
E1=IFERROR(INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH($D$1,$A$1:$A$6)),ROW($A$1:$A$6)),ROWS($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER

 

[joshman108]

Sorry, I misinterpreted!

Just take out the two references to: "|"&

Yes that works. Can you explain what the "I" is doing? It's not the letter I is it? What is it called, what is the function? And also, what is the consequence of removing it other than being able to search the entire contents of a range? I need to know in order to predict how it will handle my data.

 

[StephenCrump]

=ISNUMBER(SEARCH("ABC","xxxxABC")) is TRUE because the find_text "ABC" is being found somewhere in the within_text "xxxABC"

In contrast, if you want to limit the search to the start of the within_text string ...

=ISNUMBER(SEARCH("|"&"ABC","|"&"xxxxABC")) is FALSE. The search part of the formula is SEARCH("|ABC","|xxxxABC") which will return #VALUE.

The "|" character has no particular significance. It's just any character that you know won't occur in within_text.

If within_text could include "|" characters, you'd need to choose another character e.g. perhaps "^" or "#" or "{".

Otherwise, for example, ISNUMBER(SEARCH("|"&"ABC","|"&"xxxx|ABCxxx")) would return a false positive.

 

[joshman108]

Interesting, thank you