It is common to have codes that start with letters. In a long, sorted list scrolling can be time consuming. A link to the first code starting with a specific letter can save time. Here’s a couple of ways to implement that in Excel.
In the image below the codes are listed in column A – there are 500 alphanumeric codes.
Cell C1 has a formula that lists the letters of the alphabet. This formula creates a vertical spill range.
=CHAR(SEQUENCE(26,,65))Two solutions
Each solution has its drawbacks. Both are linked to the spill range. One solution spills, the other needs to be copied down.
XLOOKUP function
The XLOOKUP solution won’t spill to match the spill range in cell C1. Since the alphabet is unlikely to change that isn’t a problem, we can just copy the formula down.
The formula for D1 is.
=IFERROR(HYPERLINK("#"&CELL("address",XLOOKUP(C1,LEFT($A$2:$A$501),$A$2:$A$501)),"Link"),"No Link") The LEFT function converts the code list into a list of the first characters. The XLOOKUP function returns a reference to the cell that has the first code starting the with the letter. The XLOOKUP then finds the first entry.
The CELL function converts the reference that the XLOOKUP generates into a cell reference that the HYPERLINK function uses to create the hyperlink to the cell.
The IFERROR function handles the situation when no code starts with a letter.
XMATCH function
This solution will spill but the cells won’t display a blue underlined word for the hyperlink. Underlined blue words are the standard format for hyperlinks. The solution is to manually apply the hyperlink formula. Again, the list won’t expand so this is straight forward.
The formula in cell E1 is.
=IFNA(HYPERLINK("#"&"A"&XMATCH(C1#,LEFT(A2:A1000))+ROW(A1),"link"),"No Link")Again, the LEFT function converts the list into single letters. The XMATCH returns a number representing the position of the first letter. We need to adjust it as the list starts in row 2. I used the ROW function to handle the situation of a row being inserted above the Code name. An issue could arise if a row is inserted between rows 1 and 2.
The adjusted number is joined to A to create a cell reference to the first code in column A.
Last code
It is easy to tweak both the XLOOKUP and XMATCH function solutions to find the last code starting with a letter in the list. Each function has a search_mode argument. Set that to -1 to search up from the end of the list.
The amended formulas are shown below.
Cell D1
=IFERROR(HYPERLINK("#"&CELL("address",XLOOKUP(C1,LEFT($A$2:$A$501),$A$2:$A$501,,,-1)),"Link"),"No Link")Cell E1
=IFNA(HYPERLINK("#"&"A"&XMATCH(C1#,LEFT(A2:A1000),,-1)+ROW(A1),"link"),"No Link")Returning
After following a hyperlink press the F5 function key and then press Enter to return to where the hyperlink is.
Just because I am feeling ornery today, here is another formula that will produce the letters of the alphabet (it’s longer than yours, so I would use yours though)…
=BASE(SEQUENCE(26,,10),36)
As to your formulas, let me start by saying that I have never (had the need to) create a hyperlink, so my comment here is based solely on the formulas you posted. With that said, I cannot get your XMATCH formulas to work for me… I get a value in the cell that “looks” like a link, but when I click it, I get a dialog box saying “The address of this site is not valid”. Your XLOOKUP formula works fine for me, but I have to comments for it. First, it appears the error that is generated when a cell starting with the specified letter is not found seems to be #N/A. Given that, you can use the shorter IFNA function instead of the IFERROR function. Second, XLOOKUP has an option where its search can be a wildcard search. Given that, you do not need to use the LEFT function call as your function can be written like this to avoid it…
=IFNA(HYPERLINK(“#”&CELL(“address”,XLOOKUP(C1&”*”,$A$2:$A$501,$A$2:$A$501,,2)),”Link”),”No Link”)
I would note that XMATCH also has a wildcard search option, so once you get the XMATCH function to work (assuming there is something wrong with it as opposed to my screwing up implementing it), you can use that option to eliminate the LEFT function call as well. Oh, one more thing… you do not need to use absolute cell references in formulas that spill (so you can remove the $ signs in your XMATCH formula, again, once it is working).
Argh! Ignore the last sentence in my reply above… I misremembered your XMATCH function and made my comment without first checking if I was remembering correctly.