Using four criteria to retrieve information to one cell

Redfurn

New Member
Joined
Mar 17, 2011
Messages
3
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 17, 2011
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top