Index Match sorting changes cell references

[kcompton]

Hello-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Can you sort the data after you have completed an Index Match function?<o></o>
I have a large spread sheet and have applied an Index/Match function to multiple columns. The formula has both relative and absolute references. The formula works perfectly when replicated down and/or across columns.<o></o>
<o></o>
After the data was returned, I sorted the spreadsheet by a column that did not have the Index/Match formula. The data returned in the Index/Match column changed and referenced entirely different cells.<o></o>
<o></o>
Can you sort a spreadsheet that has data returned via Index/Match after applying the Index Match formula?<o></o>
<o></o>
Thank you

 

[Matty]

Hi,

Without seeing your data set or the formulas you're using, it's hard to know what could be going wrong. Could you share both so that we can try to help?

Matty

 

[kcompton]

Thank you for your response. The file in question is very large - I am recreating a portion of the file with the formula and will post it soon.

 

[kcompton]

Hello- The formula used in Cell D3 on Sheet 1 is<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=INDEX(Sheet2!$D$2:$D$17,MATCH(1,INDEX((Sheet2!$A$2:$A$17=Sheet1!A3)*(Sheet2!$B$2:$B$17=Sheet1!B3)*(Sheet2!$C$2:$C$17=Sheet1!C3),0),0))

The formula was then replicated down the column, and the Sheet1!A3, B3, and C3, references changed to match the column and row.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 24px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>SHEET 1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>SHEET 2</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Subject</TD><TD>Grade</TD><TD>Fall Quartile</TD><TD>Gains</TD><TD></TD><TD>Subject</TD><TD>Grade</TD><TD>Fall Quartile</TD><TD>Gains</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Sam</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD><TD></TD><TD>Sam</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">279.18</TD><TD></TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">279.18</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Kathy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">263.64</TD><TD></TD><TD>Kathy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">263.64</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Joe</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">170.38</TD><TD></TD><TD>Joe</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">170.38</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jean</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">254.67</TD><TD></TD><TD>Jean</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">254.67</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Peter</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">193.31</TD><TD></TD><TD>Peter</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">193.31</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Tammy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">164.34</TD><TD></TD><TD>Tammy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">164.34</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Twanda</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">102.2</TD><TD></TD><TD>Twanda</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">102.2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Chanese</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD><TD></TD><TD>Chanese</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Zach</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">500.32</TD><TD></TD><TD>Zach</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">500.32</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Sandy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">275.34</TD><TD></TD><TD>Sandy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">275.34</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Gayle</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">345.09</TD><TD></TD><TD>Gayle</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">345.09</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>Shawn</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">123.44</TD><TD></TD><TD>Shawn</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">123.44</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Vallery</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">105.25</TD><TD></TD><TD>Vallery</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">105.25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Cindy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">167.56</TD><TD></TD><TD>Cindy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">167.56</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Wendy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">268.08</TD><TD></TD><TD>Wendy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">268.08</TD></TR></TBODY></TABLE>

This is Sheet 1 after Index/Match Applied and Sorted by Subject
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>SHEET 1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Subject</TD><TD>Grade</TD><TD>Fall Quartile</TD><TD>Gains</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Chanese</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Cindy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">268.08</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Gayle</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">164.34</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Jane</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">167.56</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jean</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">254.67</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Joe</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">279.18</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Kathy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">345.09</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>Peter</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">170.38</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>Sam</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">316.24</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>Sandy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">123.44</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>Shawn</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">102.2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>Tammy</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">263.64</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>Twanda</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">193.31</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Vallery</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">105.25</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Wendy</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">500.32</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Zach</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">275.34</TD></TR></TBODY></TABLE>

As you can see, the data in column D changed;the Index/Match function did not update to aedjust to the new cell refernces need after sorting. Refreshing/recalculating did not work. Thank you for any insights to how to adjust the formula so that when the data is sorted after applying (Index/Match), the cell refererences update appropriately.

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Matty]

Hi,

I have not seen this behaviour before, but it seems to be related to the fact that you have used Sheet references for the current Sheet in your formula.

If you try:

=INDEX(Sheet2!D$2:D$17,MATCH(1,INDEX((Sheet2!A$2:A$17=A2)*(Sheet2!B$2:B$17=B2)*(Sheet2!C$2:C$17=C2),0),0))

Behaviour should be as expected (and desired).

I would normally write such a formula like this though:

=INDEX(Sheet2!D$2:D$17,MATCH(1,IF(Sheet2!A$2:A$17=A2,IF(Sheet2!B$2:B$17=B2,IF(Sheet2!C$2:C$17=C2,1))),0))

Which requires array entry, i.e. with CTRL+SHIFT+ENTER.

Matty

 

[kcompton]

Thank you for the solutions.
They both resulted in the exact results I was looking for.