Searching a table with three filters giving an adjacent value back.

[CraigA]

Hello,

I have a workbook with multiple sheets. On one sheet, I want to create a search box. This will have three cells to enter the numbers to match to (done via a drop down list). I need the result to look at a table on a different sheet, match the three numbers and return four different values.

Sheet 1
Search sheet contains three numbers. Example:
H W L
32 18 103

Sheet 2
Holds the table.
Cells A5 to AZ999 (Row 4 is the table headings)
The H (above) is in column AA, W is in AB and L is in AC.
The result I want is in column A, K, O and X.

I need some help returning the results whilst matching the three values in Sheet 1. I'd appreciate some guidance. Thanks

 

[Peter_SSs]

Not sure if your 'table' on the second sheet is a formal table (Insert ribbon tab - table) or really just a range. For the moment I have assumed a range.
I would use a helper column so we only have to identify (ie calculate) the relevant row in the table just once.

See if this is what you want

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:25px;" /><col style="width:56px;" /><col style="width:60px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >L</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">Idx</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">32</td><td style="font-size:10pt; text-align:right; ">103</td><td style="font-size:10pt; text-align:right; ">18</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">3</td><td style="font-size:10pt; text-align:right; ">56</td><td style="font-size:10pt; text-align:right; ">38</td><td style="font-size:10pt; text-align:right; ">182</td><td style="font-size:10pt; text-align:right; ">20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >Y2</td><td >=AGGREGATE(15,6,<span style=' color:008000; '>(ROW<span style=' color:#0000ff; '>(Sheet2!A$5:A$100)</span>-ROW<span style=' color:#0000ff; '>(Sheet2!A$5)</span>+1)</span>/<span style=' color:008000; '>(<span style=' color:#0000ff; '>(Sheet2!AA$5:AA$100=H2)</span>*<span style=' color:#0000ff; '>(Sheet2!AB$5:AB$100=W2)</span>*<span style=' color:#0000ff; '>(Sheet2!AC$5:AC$100=L2)</span>)</span>,1)</td></tr><tr><td >Z2</td><td >=INDEX(Sheet2!A$5:A$100,Y2)</td></tr><tr><td >AA2</td><td >=INDEX(Sheet2!K$5:K$100,Y2)</td></tr><tr><td >AB2</td><td >=INDEX(Sheet2!O$5:O$100,Y2)</td></tr><tr><td >AC2</td><td >=INDEX(Sheet2!X$5:X$100,Y2)</td></tr></table></td></tr></table>

 

[CraigA]

Hi, this is spot on thank you. I am just trying to work it out. It doesn't work for me yet, the Y2 cell formula returns #NUM ! currently. The others have returned a static reference which doesn't change when I change the inputs on sheet 1. Leave it with me and i'll work on it some more. Thank you again.

 

[Peter_SSs]

The NUM error in Y2 would indicate no rows in the Sheet 2 range that match the H, L & w values.
Check that I am looking in the correct columns.
I am looking for the H2 value in column AA of Sheet2, the W2 value in column AB of Sheet2 and the L2 value in column AC of Sheet2 and all 3 values must be on the same row in Sheet2.

Here is part of my Sheet2 showing what was matched (yellow, blue, green) and what was returned (amber). (Note that I have hidden most columns to keep this small)

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >K</td><td >O</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">142</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; text-align:right; ">75</td><td style="font-size:10pt; text-align:right; ">119</td><td style="font-size:10pt; text-align:right; ">94</td><td style="font-size:10pt; text-align:right; ">60</td><td style="font-size:10pt; text-align:right; ">125</td><td style="font-size:10pt; text-align:right; ">130</td><td style="font-size:10pt; text-align:right; ">77</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">53</td><td style="font-size:10pt; text-align:right; ">49</td><td style="font-size:10pt; text-align:right; ">136</td><td style="font-size:10pt; text-align:right; ">77</td><td style="font-size:10pt; text-align:right; ">61</td><td style="font-size:10pt; text-align:right; ">190</td><td style="font-size:10pt; text-align:right; ">196</td><td style="font-size:10pt; text-align:right; ">81</td><td style="font-size:10pt; text-align:right; ">5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">56</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">38</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">182</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">20</td><td style="font-size:10pt; text-align:right; ">113</td><td style="font-size:10pt; text-align:right; ">139</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">32</td><td style="background-color:#00ccff; font-size:10pt; text-align:right; ">18</td><td style="background-color:#99cc00; font-size:10pt; text-align:right; ">103</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; text-align:right; ">54</td><td style="font-size:10pt; text-align:right; ">13</td><td style="font-size:10pt; text-align:right; ">32</td><td style="font-size:10pt; text-align:right; ">188</td><td style="font-size:10pt; text-align:right; ">131</td><td style="font-size:10pt; text-align:right; ">102</td><td style="font-size:10pt; text-align:right; ">79</td><td style="font-size:10pt; text-align:right; ">23</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">22</td><td style="font-size:10pt; text-align:right; ">67</td><td style="font-size:10pt; text-align:right; ">132</td><td style="font-size:10pt; text-align:right; ">141</td><td style="font-size:10pt; text-align:right; ">186</td><td style="font-size:10pt; text-align:right; ">107</td><td style="font-size:10pt; text-align:right; ">18</td><td style="font-size:10pt; text-align:right; ">152</td><td style="font-size:10pt; text-align:right; ">123</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">81</td><td style="font-size:10pt; text-align:right; ">89</td><td style="font-size:10pt; text-align:right; ">55</td><td style="font-size:10pt; text-align:right; ">97</td><td style="font-size:10pt; text-align:right; ">51</td><td style="font-size:10pt; text-align:right; ">69</td><td style="font-size:10pt; text-align:right; ">9</td><td style="font-size:10pt; text-align:right; ">97</td><td style="font-size:10pt; text-align:right; ">21</td></tr></table>


And here is my Sheet1 again to show the matching colours.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:33px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:25px;" /><col style="width:36px;" /><col style="width:95px;" /><col style="width:95px;" /><col style="width:95px;" /><col style="width:95px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >H</td><td >L</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">Idx</td><td style="font-size:10pt; text-align:right; ">From col A</td><td style="font-size:10pt; text-align:right; ">From col K</td><td style="font-size:10pt; text-align:right; ">From col O</td><td style="font-size:10pt; text-align:right; ">From col X</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; font-size:10pt; text-align:right; ">32</td><td style="background-color:#99cc00; font-size:10pt; text-align:right; ">103</td><td style="background-color:#00ccff; font-size:10pt; text-align:right; ">18</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:center; ">3</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">56</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">38</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">182</td><td style="background-color:#ffcc00; font-size:10pt; text-align:right; ">20</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >Y2</td><td >=AGGREGATE(15,6,<span style=' color:008000; '>(ROW<span style=' color:#0000ff; '>(Sheet2!A$5:A$100)</span>-ROW<span style=' color:#0000ff; '>(Sheet2!A$5)</span>+1)</span>/<span style=' color:008000; '>(<span style=' color:#0000ff; '>(Sheet2!AA$5:AA$100=H2)</span>*<span style=' color:#0000ff; '>(Sheet2!AB$5:AB$100=W2)</span>*<span style=' color:#0000ff; '>(Sheet2!AC$5:AC$100=L2)</span>)</span>,1)</td></tr><tr><td >Z2</td><td >=INDEX(Sheet2!A$5:A$100,Y2)</td></tr><tr><td >AA2</td><td >=INDEX(Sheet2!K$5:K$100,Y2)</td></tr><tr><td >AB2</td><td >=INDEX(Sheet2!O$5:O$100,Y2)</td></tr><tr><td >AC2</td><td >=INDEX(Sheet2!X$5:X$100,Y2)</td></tr></table></td></tr></table>