'How do I sort Excel without affecting INDEX-MATCH row-based formulas?
So I have this useful multi-conditional INDEX-MATCH array formula working over two tabs (CTRL-Shift-ENT each time for other column tweaks):
=INDEX(Data!$G$1:$G$2086,MATCH(1,(Results!$C5=Data!$L$1:$L$2086)*(Results!$D5=Data!$B$1:$B$2086),0))
I can take those results and copy into new sheet and work from there. I WANT to be able to Sort on any column and not affect the results, since the "5" needs to self-reference its row as it sorts to another row. So trying to embed a ROW and/or INDIRECT function.
For example, =INDIRECT("$C"&ROW()) in a blank cell gives me the result of cell C5 in row 5, but however I wedge it into the formula gives an error. Taking the "$" out next to "C" and "D" does not help. Not sure where I need or not need parentheses, or if because an array need to add something else (an @ with the row function?)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|