Find Latest Revision and Return Hyperlink using a Column to Search

hopefully one of you can help me out.

I'm making an Excel spreadsheet that imports a data file and compares the data with a couple of other sources, including file names, in a very large folder. The goal right now is to take a part number (text), find that part number in the folder query, combine the folder path with the file name, and create a hyperlink.

I'm almost done with the spreadsheet, but I can't figure out the hyperlink function. The issue is we have a revision process, so the first revision is "Rev01 (a)(b), PartNumber" and then "Rev02 (a)(b), PartNumber". Most of the search/index functions I've seen use MAX() which only works with values not text. And the rest don't work at all.

Part Number to Lookup	FileName	Path
PartNumber1	Rev01 (a)(b), PartNumber1	C:\
PartNumber2	Rev02 (a)(b), PartNumber1	C:\
	Rev01 (a)(b), PartNumber2	C:\

(One big thing for me is I only use spill formulas so I can reuse the sheet without worrying about dragging down formulas)

After a few more hours of searching and trying new formulas I found that if you use =IF(ISNUMBER(SEARCH(A2,B2:B4)),B2:B4,"") you get all FilesNames with the first PartNumber.

Result1
Rev01 (a)(b), PartNumber1
Rev02 (a)(b), PartNumber1

So, using BYROW and LAMBDA I built this monstrosity:

=HYPERLINK(BYROW(A2:A3,LAMBDA(row,LET(partnum,IF(ISNUMBER(SEARCH(row,B2:B4)),C2:C4&B2:B4,""),filtered,FILTER(partnum,partnum<>""),INDEX(filtered,COUNTA(filtered))))))

The key idea for me was to use Result1 as an array and INDEX the new array to get the last value in the array (must be sorted in ascending order in this case which is easy with LET:

INDEX(filtered,COUNTA(filtered))

header 1
C:\Rev02 (a)(b), PartNumber1
C:\Rev01 (a)(b), PartNumber2

Now, my question is HYPERLINK() is only hyperlinking the first cell is there a way to hyperlink all of the cells? Also, is there an easier way to do this? This seems a bit complicated, but I could not find anything online.

Solution

Spilling for HYPERLINK

A possible workaround for using HYPERLINK with spilled arrays: pre-fill the range for links, accommodating for largest result set with first cell pointing to the cell with spill formula.

As in the example below:

In D2 enter HYPERLINK(E2), fill down
In E2 enter spill formula, with output in the form of
CONCAT("[", INDEX(filtered, COUNTA(filtered)), "]") instead of HYPERLINK(CONCAT…

This way we don't have to fill-down what's already spilled :)

If you haven't already, could you try this:

=BYROW(A2:A3, LAMBDA(row,
LET(partnum, IF(ISNUMBER(SEARCH(row, B2:B4)), C2:C4 & B2:B4, ""),
filtered, FILTER(partnum, partnum <> ""),
HYPERLINK(CONCAT("[", INDEX(filtered, COUNTA(filtered)), "]")))))

Wrapping the link in [] is needed when the path has spaces.
In my testing the links are not formatted (with blue underlining) consistently (but the cursor turns to a pointer when hovering over), but are functioning correct as links.

Please test and let know.

Start a new line of text inside a cell in Excel - Microsoft Support

Optional - extract revision numbers to find max

Since you seem to like LAMBDAs, defining a helper function first_number in Name Manager to extract the revision number could be helpful. It's quickly put together function, you may find shorter ones elsewhere. My preferred way it to use REGEX, but it may not be available in your version of Excel yet.

first_number defined in Name Manager:

=LAMBDA(text,
    LET(
        len_, LEN(text),
        num_start, MIN(MAP(SEQUENCE(len_), LAMBDA(i, IF(ISNUMBER(--MID(text, i, 1)), i, len_ + 1)))),
        num_end, MIN(
            MAP(
                SEQUENCE(len_ - num_start + 1, , num_start),
                LAMBDA(i, IF(ISNUMBER(--MID(text, i, 1)), len_ + 1, i))
            )
        ),
        --MID(text, num_start, num_end - num_start)
    )
)

Then, instead of using the Part Number column, TEXTSPLIT can be used to extract the Part Number and Revision.
After extracting use the custom function to get max number

=LET(
    file_names, TOCOL(B2:B10, 1),
    paths, TOCOL(C2:C10, 1),
    revisions, TOCOL(TRIM(BYROW(file_names, LAMBDA(row, TEXTBEFORE(row, ",")))), 2),
    part_nums, TOCOL(TRIM(BYROW(file_names, LAMBDA(row, TEXTAFTER(row, ",")))), 2),
    rev_numbers, MAP(revisions, first_number),
    make_hyperlink, LAMBDA(path_and_file, HYPERLINK(CONCAT("[", path_and_file, "]"))),
    make_links, MAP(
        UNIQUE(part_nums),
        LAMBDA(part_num,
            LET(
                max_rev_num, MAX(FILTER(rev_numbers, part_nums = part_num)),
                path_and_file_for_latest, FILTER(
                    HSTACK(paths, file_names),
                    (part_nums = part_num) * (rev_numbers = max_rev_num)
                ),
                make_hyperlink(path_and_file_for_latest)
            )
        )
    ),
    make_links
)