Complex 3 way lookup

[shemayisroel]

Hi,


Sample data is below.

The data table is from A1:D10

The lookup criteria is from A12:C13

What I want to do is a lookup on all the three different criteria and return all the results from Column D in the data table (Comments) I have higlighted my expected result in green and placed the actual results in D13,D14

The type of solution I'm looking for is similar to http://www.mrexcel.com/forum/showthread.php?t=362743



<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><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: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">SCORE</TD><TD style="TEXT-ALIGN: center">YEAR</TD><TD style="TEXT-ALIGN: center">DEP</TD><TD style="TEXT-ALIGN: center">Comment</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2009</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">Good</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">IT</TD><TD>Ok</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2007</TD><TD style="TEXT-ALIGN: center">Mgmt</TD><TD>Not Bad</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">IT</TD><TD>Thanks</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">Mgmt</TD><TD>Excellent</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2007</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Hmmm</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Cool</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2008</TD><TD style="TEXT-ALIGN: center">CC</TD><TD>Bad</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">2009</TD><TD style="BACKGROUND-COLOR: #ccffcc; TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">No Comment</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Score Look Up</TD><TD style="TEXT-ALIGN: center">Year Look Up</TD><TD style="TEXT-ALIGN: center">Dep Look Up</TD><TD style="TEXT-ALIGN: center">RESULT</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2009</TD><TD style="TEXT-ALIGN: center">IT</TD><TD style="BACKGROUND-COLOR: #ccffcc">Good</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD> </TD><TD> </TD><TD> </TD><TD style="BACKGROUND-COLOR: #ccffcc">No Comment</TD></TR></TBODY></TABLE>

 

[sanrv1f]

Look familiar Sankar?

very much

 

[T. Valko]

is Index non-volatile?

INDEX is not a volatile function however, there are certain uses of INDEX that might lead you to believe that it is volatile.

Try this in a test file:

Enter some random numbers in A2:A10 in a contiguous range.

Enter this formula in C2:

=SUM(A2:INDEX(A2:A100,COUNT(A2:A100)))

Save the file then close it.

Wait a minute or 2 then open the file. Don't do anything while the file is open. Now close the file.

You'll get prompted to save changes. But, you didn't make any changes so why are you getting the save changes prompt? You might think that there must be a volatile function causing this. Nope!

In this case when you open the file the calculation dependency tree is rebuilt and for some reason the way INDEX is being used to define a range Excel registers that calculation as a change thus the save changes prompt.