'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Excel INDEX and MATCH to Link Related Data After Sorting
Question details
The user needs to use a lookup formula to return data associated with a specific key value, ensuring the links remain accurate even if the source data list is sorted.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up and linking related data across columns dynamically without relying on fixed cell references.
- Observed behavior
- The user requires a stable lookup solution so that sorting the source data does not break the linked values in the destination cells.
Ensure your source data has a unique key column to serve as the lookup value, and verify that there are no merged cells within your data range before applying the lookup formula.
Use INDEX, MATCH, and COLUMN Formulas for Dynamic Lookups
This method combines INDEX, MATCH, and COLUMN functions to reliably fetch related data even if the original dataset changes its sorted order.
Combining these three functions creates a robust, two-way lookup. The formula dynamically recalculates the exact row and column position of your data, rendering it immune to sorting changes.
Identify your key column (e.g., A1:A4), the related data range (e.g., B1:D4), and the specific lookup value you want to search for in your destination area (e.g., F1).
In your destination cell (e.g., G1), input the formula: =INDEX($B$1:$D$4,MATCH($F1,$A$1:$A$4,0),COLUMN(G1)-COLUMN($G1)+1).
Press Enter to execute the formula. Click on cell G1, grab the fill handle (the small square at the bottom right of the cell), and drag it to the right to copy the formula across adjacent columns.
Highlight your original source list and apply a new sort order from the Data tab. Check your destination cells to confirm that the retrieved data correctly tracks the key value without errors.
Use WPS Spreadsheet for Advanced Data Lookups
WPS Spreadsheet fully supports advanced lookup formulas like INDEX and MATCH, allowing you to seamlessly process complex datasets. It provides high performance for large data files and guarantees stability across all calculations.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data lists you need to link.
- 2. Apply the lookup formula: Select the destination cell and type =INDEX(array, MATCH(lookup_value, lookup_array, 0), column_num) to dynamically retrieve your data.
- 3. Drag to copy: Use the fill handle at the bottom right corner of the active cell to drag the formula across or down, instantly applying it to other cells.
Frequently Asked Questions
Why does VLOOKUP sometimes break when sorting, but INDEX and MATCH do not?
VLOOKUP often relies on a hardcoded column index number and requires the lookup column to be the first one on the left. INDEX and MATCH dynamically find the exact row and column regardless of structural sorting or insertion, making it far more resilient.
What does the '0' signify in the MATCH function?
The '0' as the third argument in the MATCH function specifies an Exact Match. It tells the spreadsheet to look for exactly the key value you specified. If it cannot find the exact key, it returns an #N/A error.
Why is the COLUMN() function used in this formula?
Using COLUMN(G1)-COLUMN($G1)+1 acts as a dynamic counter. It automatically increments the column index number for the INDEX function as you drag the formula to the right, saving you from having to manually change the column number for each adjacent cell.
Can I use XLOOKUP instead of INDEX and MATCH?
Yes, if you are using WPS Spreadsheet or a newer version of Excel, XLOOKUP provides a simpler and more powerful way to look up related data dynamically without needing to nest two separate functions.
