How to Use XMATCH in an Excel Hyperlink to Find the First Blank Cell
Question details
The user wants to create a dynamic hyperlink formula that refers to a worksheet name selected from a drop-down list and navigates directly to the first blank cell in column A of that specific sheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic navigation link using a drop-down menu to speed up data entry by jumping to the next empty row in a selected worksheet.
- Observed behavior
- The user needs a combined formula using HYPERLINK, ADDRESS, XMATCH, and INDIRECT to achieve this dynamic navigation.
Ensure your workbook contains the target worksheets and that you have set up a cell with a Data Validation drop-down list containing the exact names of those worksheets.
Use a Combined Formula to Create a Dynamic Hyperlink
Use a nested formula to dynamically look up the sheet name, find the empty cell, and generate a clickable link.
This method leverages the XMATCH function to locate the first empty string in a specific column, while INDIRECT allows you to reference sheet names dynamically from a drop-down cell.
The ADDRESS function then constructs the exact cell reference for the HYPERLINK function to navigate to.
Select cell B2. Go to the Data tab, click Data Validation, choose List, and enter your worksheet names as the source.
In the cell where you want the hyperlink to appear, enter the formula: =HYPERLINK("#"&ADDRESS(XMATCH("",INDIRECT("'"&B2&"'!A:A")),1,,,B2),"Go to first blank cell").
Select a different sheet name from the drop-down in B2 and click the generated link to ensure it navigates to the first blank cell in column A of the chosen sheet.
Use Advanced Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XMATCH, INDIRECT, and HYPERLINK. You can build powerful, dynamic navigation links within your workbooks effortlessly.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the target worksheets.
- 2. Insert the Dynamic Formula: Type =HYPERLINK("#"&ADDRESS(XMATCH("",INDIRECT("'"&B2&"'!A:A")),1,,,B2),"Jump to Blank") in your desired cell.
- 3. Use the Link: Click the hyperlink to instantly navigate to the first blank cell in the designated sheet.
Frequently Asked Questions
Why is my HYPERLINK formula returning a 'Reference isn't valid' error?
This usually happens if the sheet name in cell B2 doesn't exactly match the actual sheet name, or if there are trailing spaces. Also, verify that the '#' symbol is included at the beginning of the link location argument.
Can I use MATCH instead of XMATCH to find the first blank cell?
Yes, you can use MATCH(TRUE, INDIRECT("'"&B2&"'!A:A")="", 0) entered as an array formula, but XMATCH is more efficient because it handles exact matches natively and searches from the top down by default without requiring Ctrl+Shift+Enter.
How do I change the column being searched to column C instead of A?
In the INDIRECT function, change "'!A:A" to "'!C:C". Also, in the ADDRESS function, change the column number argument from 1 to 3, like this: ADDRESS(..., 3, , , B2).
