Multi-Cell Lookup Search Results

[qlander]

hi,



I'm having difficulty creating a formula using INDIRECT to cross reference a series of relevant rows (columnA) and searching for specific criteria found in another sheet, and once found outputting each result.



eg: using a csv for the data



Sheet 1 will have the data:



"Reference","Text"

312,"This Is The Text To Search - for AAA"

316,"Some other Text tosearch - for ABA"

319,"Some other text to search for"

312,"other text to search for XXXXXX"

391,"Some - for AaA other text"

312,"this is some - for BDDDFLk data text"

316,"More text other for it search - for AAA"

312,"Text 1 2 3 4 5, for AAA, - for 12345,ro2 extras"

316,"more than other to text"

311,"more other text search"

316," This other has more than text to search for"

312," This - for atestto is for a test but - for including this text"



Sheet 2 will have the extraction:



"Reference","Start Search","End Search","Result 1", "Result 2", "Result 3",.....etc..

312," - for "," ","AAA","BDDDFLk","12345,ro2",atestto","including"

316," other ","search","Text to ","for it ","has more than text to "

....etc..


(Please note: forum italic just used to highlight extraction criteria and bold used to highlight data extracted).


I'm getting stuck on how to approach this - ie multiple VLOOKUP results using Column A matching? and then what?

Needs to be a non-array'd excel formula (due to large size of data), unfortunately not using VB (due to restrictions), etc..



Anyone able to help at all?



Thanks

 

[dave2018]

sorry, because you posted your data not in a table structure, i wanst able to recreate exactly a %100 working formula..

but here is a good start:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K
1	Reference	Text
2				Reference	Start Search	End Search	Result 1	"Result 2"	"Result 3"	.....etc..
3	312	This Is The Text To Search - forAAA		316	other	search	Text to	for it	#VALUE!	#VALUE!	#NUM!
4	316	SomeotherText tosearch- for ABA
5	319	Some other text to search for
6	312	other text to search for XXXXXX
7	391	Some - for AaA other text
8	312	this is some- forBDDDFLkdata text
9	316	More textotherfor itsearch- for AAA
10	312	Text 1 2 3 4 5, for AAA,- for12345,ro2extras
11	316	more than other to text
12	311	more other text search
13	316	Thisotherhas more than text tosearchfor
14	312	This- foratesttois for a test but- forincludingthis text
Sheet3

Cell Formulas
Range	Formula
G3	{=MID(INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1),COLUMNS($G$2:G2))),FIND($E3,INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1),COLUMNS($G$2:G2))))+LEN($E3),(FIND($F3,INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1),COLUMNS($G$2:G2))),FIND(E3,INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1),COLUMNS($G$2:G2))))))-(FIND($E3,INDEX($B$3:$B$14,SMALL(IF($A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1),COLUMNS($G$2:G2))))+LEN($E3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dave2018]

if you can use helper cells, that would shorten this formula immensely

 

[qlander]

sorry, because you posted your data not in a table structure, i wanst able to recreate exactly a %100 working formula..
...
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]G3[/TH]
[TD="align: left"]{=MID([COLOR=rgb(255]INDEX($B$3:$B$14,SMALL([COLOR=0)]IF([COLOR=rgb(0]$A$3:$A$14=$D3,ROW($A$3:$A$14)-ROW($A$3)+1[/COLOR]),COLUMNS([COLOR=rgb(0]$G$2:G2[/COLOR])[/COLOR])),FIND($E3,INDEX([COLOR=0)]$B$3:$B$14,SMALL([COLOR=rgb(0]IF($A$3:$A$14=$D3,ROW([COLOR=255)]$A$3:$A$14[/COLOR])-ROW([COLOR=255)]$A$3[/COLOR])+1),COLUMNS($G$2:G2)[/COLOR])[/COLOR]))+LEN($E3),(FIND([COLOR=0)]$F3,INDEX([COLOR=rgb(0]$B$3:$B$14,SMALL(IF([COLOR=255)]$A$3:$A$14=$D3,ROW([COLOR=rgb(0]$A$3:$A$14[/COLOR])-ROW([COLOR=rgb(0]$A$3[/COLOR])+1[/COLOR]),COLUMNS([COLOR=255)]$G$2:G2[/COLOR]))[/COLOR]),FIND([COLOR=rgb(0]E3,INDEX($B$3:$B$14,SMALL([COLOR=255)]IF([COLOR=rgb(0]$A$3:$A$14=$D3,ROW([COLOR=rgb(255]$A$3:$A$14[/COLOR])-ROW([COLOR=rgb(255]$A$3[/COLOR])+1[/COLOR]),COLUMNS([COLOR=rgb(0]$G$2:G2[/COLOR])[/COLOR]))[/COLOR])[/COLOR]))-(FIND([COLOR=0)]$E3,INDEX([COLOR=rgb(0]$B$3:$B$14,SMALL(IF([COLOR=255)]$A$3:$A$14=$D3,ROW([COLOR=rgb(0]$A$3:$A$14[/COLOR])-ROW([COLOR=rgb(0]$A$3[/COLOR])+1[/COLOR]),COLUMNS([COLOR=255)]$G$2:G2[/COLOR]))[/COLOR])[/COLOR])+LEN([COLOR=0)]$E3[/COLOR]))[/COLOR])}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Wow that was quick! Thanks

This formula works well with small data sets. Unfortunately because I'm using big-data (60k+ cells) it lags a lot, thus the use of INDIRECT to avoid arrays (Ctrl+Shift+Enter). I'll see if I can adapt and re-post if possible.

 

[dave2018]

my code doesnt use indirect. Did you mean you wanted a faster formula?

Glad we are on the right track .

 

[qlander]

my code doesnt use indirect. Did you mean you wanted a faster formula?

Glad we are on the right track .

Yes, indirect avoids the use of array formula's since I'm using a large data set, arrays just freeze up the sheet. The formula works well with a small data set otherwise (after correcting some minor typo's).