Using four criteria to retrieve information to one cell

[Redfurn]

Firstly sorry for the confusing title. I could not think of anything better to use as a short description for my issue, haha.

I have a spreadsheet loaded with information and results based on a number of selections made by certain people.

Column A has the title of each possible result labelled A1 - A169
Column B has the first choice, A-E 2-9
Column C has the second choice, A-E 2-9
Column D has the third choice, Y or N
Columns E-M are titled with 1-9 and have different reluslts between 5-85 for each possible result.

For instance the first row of results would read something like;

<TABLE style="WIDTH: 333pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=447 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 1755" width=48><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" span=9 width=23><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=45>A</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 36pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=48>A</TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=83>N</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>85</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>73</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>64</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>56</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>49</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>44</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>39</TD><TD class=xl64 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>35</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 17pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=23>31</TD></TR></TBODY></TABLE>

I want to be able to select ONE result depending on what combination of the four variables is used. I have set aside a seperate section for these. It looks like this.

<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=213 border=0><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" span=2 width=45><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=45 height=20>Choice 1</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=45>Choice 2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 35pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=47>Choice 3</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=76>People</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">N</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=76>2</TD></TR></TBODY></TABLE>

Given these choices, the result shown (in a seperate cell) should be 73.

<TABLE style="WIDTH: 116pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=154 border=0><COLGROUP><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=154 height=20>Result</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 116pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=154 height=20>73</TD></TR></TBODY></TABLE>

I am struggling to figure out what formula(s) to use to extract these results. There is plenty of info as you can imagine, 169 rows x 9 columns. So I am looking to make it as simple and inclusive as possible.

Is this possible as a formula, or should I look into creating a Macro?

Last thing. It is essential to allow individual selections of each choice. I cannot simply use the name of each possible result in cloumn A to make the selection.

Thank you in advance for any help I know I am pushing my good will posting such an issue for my first post. But I have run out of brain power on this one guys!

 

[Haseeb Avarakkan]

Hello, Welcome to the board. Try

<b>Excel 2007</b><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 /><col /><col /><col /><col /><col /><col /><col /><col /><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><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Field 1</td><td style="font-weight: bold;;">Field 2</td><td style="font-weight: bold;;">Field 3</td><td style="font-weight: bold;text-align: right;;">1</td><td style="font-weight: bold;text-align: right;;">2</td><td style="font-weight: bold;text-align: right;;">3</td><td style="font-weight: bold;text-align: right;;">4</td><td style="font-weight: bold;text-align: right;;">5</td><td style="font-weight: bold;text-align: right;;">6</td><td style="font-weight: bold;text-align: right;;">7</td><td style="font-weight: bold;text-align: right;;">8</td><td style="font-weight: bold;text-align: right;;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Choice 1</td><td style="font-weight: bold;;">Choice 2</td><td style="font-weight: bold;;">Choice 3</td><td style="font-weight: bold;;">People</td><td style="font-weight: bold;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">A</td><td style=";">A</td><td style=";">N</td><td style="text-align: right;;">85</td><td style="text-align: right;;">73</td><td style="text-align: right;;">64</td><td style="text-align: right;;">56</td><td style="text-align: right;;">49</td><td style="text-align: right;;">44</td><td style="text-align: right;;">39</td><td style="text-align: right;;">35</td><td style="text-align: right;;">31</td><td style="text-align: right;;"></td><td style=";">A</td><td style=";">A</td><td style=";">N</td><td style="text-align: right;;">2</td><td style="text-align: right;;">73</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">A</td><td style=";">B</td><td style=";">Y</td><td style="text-align: right;;">10</td><td style="text-align: right;;">12</td><td style="text-align: right;;">14</td><td style="text-align: right;;">16</td><td style="text-align: right;;">18</td><td style="text-align: right;;">20</td><td style="text-align: right;;">22</td><td style="text-align: right;;">24</td><td style="text-align: right;;">26</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">A</td><td style=";">A</td><td style=";">Y</td><td style="text-align: right;;">15</td><td style="text-align: right;;">20</td><td style="text-align: right;;">25</td><td style="text-align: right;;">30</td><td style="text-align: right;;">35</td><td style="text-align: right;;">40</td><td style="text-align: right;;">45</td><td style="text-align: right;;">50</td><td style="text-align: right;;">55</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><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><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>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">R2</th><td style="text-align:left">{=INDEX(<font color="Blue">D1:L169,MATCH(<font color="Red">1,IF(<font color="Green">A1:A169=N2,IF(<font color="Purple">B1:B169=O2,IF(<font color="Teal">C1:C169=P2,1</font>)</font>)</font>),</font>),Q2</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 />
Confirmed with Control+Shift+Enter, not just Enter.

 

[Redfurn]

So much simpler than I had working out in my mind. This is the first time I have really used EXCEL, I have no training and am trying to teach myself with a few personal projects.

I thank you very much for your help Haseeb, I would have driven myself crazy trying to work it out on my own.