Getting information from table plus information in top row and first column

[Phil Smith]

I have a worksheet containing the following:

Names - Filling B1:BG1 (Top row)

Dates - filling A2:A59 (First Column)

Table of data - Filling B2:BG59

My end goal is that the user, knowing any two of these three things and after entering the two knowns, will be shown the third unknown.

The examples I will give will use the name in B1, the date in A2, and the data in B2.

First Option
They know a name, in B1.
They know a date, in A2.
They are shown the data in B2.

Second Option
They know a date, in A2.
They know the data, in B2.
They are shown the name in B1.

Third Option
They know the date in B2.
They know the name in B1.
They are shown the date in A2.

My stumbling blocks are the formulae to extract the third unkown in each situation based on the other two known cells.

I have a feeling INDEX and MATCH may be involved, possibly VLOOKUP, but I just CANNOT get my head around it, even after looking over a previous post of mine for a similar situation, and am asking your help - again! Sorry!

Thanks in advance.

Phil

 

[AlphaFrog]

Sheet3
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Joe</td><td style="background-color: #FFFF99;;">Sam</td><td style=";">Bob</td><td style=";">Jim</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">6/25/2011</td><td style="text-align: right;;">1</td><td style="text-align: right;;">11</td><td style="text-align: right;;">21</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;background-color: #FFFF99;;">6/26/2011</td><td style="text-align: right;;">2</td><td style="text-align: right;background-color: #FFFF99;;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">6/27/2011</td><td style="text-align: right;;">3</td><td style="text-align: right;;">13</td><td style="text-align: right;;">23</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">6/28/2011</td><td style="text-align: right;;">4</td><td style="text-align: right;;">14</td><td style="text-align: right;;">24</td><td style="text-align: right;;">34</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;;">LookUp</td><td style="font-weight: bold;;">Values</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;;">Results</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;;">Name</td><td style=";">Sam</td><td style="text-align: right;;"></td><td style="background-color: #FFFF99;;">Sam</td><td style=";">2nd Option</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;;">Date</td><td style="text-align: right;;">6/26/2011</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF99;;">6/26/2011</td><td style=";">3rd Option</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;;">Data</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF99;;">12</td><td style=";">1st Option</td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=INDEX(<font color="Blue">Data,MATCH(<font color="Red">B10,Dates,0</font>),MATCH(<font color="Red">B9,Names,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">{=INDEX(<font color="Blue">Names,MIN(<font color="Red">IF(<font color="Green">(<font color="Purple">Dates=B10</font>)*(<font color="Purple">Data=B11</font>),COLUMN(<font color="Purple">Names</font>)-1</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">{=INDEX(<font color="Blue">Dates,MIN(<font color="Red">IF(<font color="Green">(<font color="Purple">Names=B9</font>)*(<font color="Purple">Data=B11</font>),ROW(<font color="Purple">Dates</font>)-1</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Data</th><td style="text-align:left">=Sheet3!$B$2:$E$5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Dates</th><td style="text-align:left">=Sheet3!$A$2:$A$5</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">Names</th><td style="text-align:left">=Sheet3!$B$1:$E$1</td></tr></tbody></table></td></tr></table><br />

 

[Phil Smith]

Thank for your reply.

I am slowly trying to convert it to my worksheet and it seems to be working so far.

I don't understand how an array formula works though. Could you shed any light on that?

But once agian, thanks for your help!

Phil

 

[AlphaFrog]

Array Formulas