Can a Partial Search Array Formula for Multiple return values be looped?

[RiChCh]

Hi! First time poster here. I’ve read through some threads on these forums which have helped me before (Thanks!) but I couldn’t find a solution to my current dilemma, I've been fighting with this issue all day and hoping maybe someone could give me a hand.

I have 2 sets of data I am trying to compare, which will give me multiple returns. My goal is to get a list of the all 3 fields (Document Number, Project, and Filename) on one spreadsheet. (See attachment please)

Sheet 1: Contains the Document Number that ends up being a partial value (search parameter)
Sheet2: Contains the Project and Filenames (filenames contain the document number and random characters) I have to reference
Sheet 3: Needs to show the Document Number, Project #, and associated Filename

Array Formula is as follows in C2 (to find the corresponding filenames):
=INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1)))
Array Formula in B2 (to find the corresponding project):
=INDEX(Sheet2!$A$2:$A$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1)))

This lets me find all “Filename” items in Sheet2 with the “Document Number” in it and lists it, along with the project, in Sheet 3. However, how would I get the next “Document Number” in Sheet1 to become the search parameter when the return is #NUM? Until all the “document numbers” in Sheet1 are depleted?

I’ve tried the following and a few variations of it, which doesn’t work. =ISERROR(INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH(Sheet1!$A$2,Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1))), =INDEX(Sheet2!$B$2:$B$23,SMALL(IF(ISNUMBER((SEARCH((Sheet1!$A$2+ROW()+1),Sheet2!$B$2:$B$23))),ROW(Sheet2!$B$2:$B$23)-MIN(ROW(Sheet2!$B$2:$B$23))+1,""),ROW(A1))))

And then on top of that I also need to figure out how to add the “Document Number”(partial value) from Sheet1 into ColumnA in Sheet3 in direct correlation to the value in ColumnC Sheet3.

I may be doing it backwards, and if so, someone please point it out. Maybe I should put all this in a macro instead?

Disclaimer: This is a small sample, this is actually list of 5000+ files.

Thanks for any help you can provide. Sorry if it’s a convoluted post, I’m brain dead at the moment.

[GC Excel]

Hello RiChCh,

I prefer VBA to formulas...
Here's a suggestion for your :

Please Login or Register  to view this content.

[azumi]

Pergaps something like this? Iam using helper columns on sheet2

[MarvinP]

Hi RiChCh and welcome to the forum,

This looks like an Advanced Filter problem where you use your sheet 1 numbers in a wildcard search on the second sheet. See the attached and learn a bit more about Advanced Filters and Wildcard searches.

Adv Filter using Wildcards.xlsx

[RiChCh]

Pergaps something like this? Iam using helper columns on sheet2

Thanks Azumi,

I kind of see what you did here, and it works for this example. However my actual data contains alphas as well so I don't think this would work in that situation.

Richch

[RiChCh]

Hello RiChCh,

I prefer VBA to formulas...
Here's a suggestion for your :

Please Login or Register  to view this content.

Thanks GC.

This worked! I admit my ignorance at how the VBA coding works, so I have no clue how it works! lol

Quick question, if I wanted to add a column for version in column B !Sheet1, how could we go about adding this info to the VBA macro?
Also, it adds the information into columns E-G, is this due to the part I highlighted? It doesn't matter really, as all I need is the output, just curious.

Thanks,
Richch

[GC Excel]

1)
How would the new column B in sheet 1 relate to the data in sheet 2? Can you provide example.

2)
Yes, output is in E-G because of what you highlighted. I didn't want to write over your data.
simply change the number to 1, 2, 3 respectively
Cells(n, 5) means Cell(row N, column 5)