Using Match to get data from the visible part of the table

[rangerrambo]

So, I as the title says I am trying to have Match only look into the filtered part of the table data.

I am doing some interpolation depending of range into the elev value.

The problem is that I have multiple type Bs and multiple table types, which contain multiple of the same values in the range rows, for example here we have twice 250, but the data tables is a lot bigger than what i posted and has whole ranges duplicated. like 300 350 400 450 500 then again maybe 350 400 450 500 550 600 750 etc.

I was hoping that I could select the parameters through filters and use match on the visible part of the table, but that doesn't work.

I am just looking for some tips how to solve the problem. I guess I could create different offset for each subtable but I don't know.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">distance</td><td style="text-align: right;;"></td><td style="text-align: right;;">244</td></tr><tr ><td style="color: #161120;text-align: center;">37</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;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Elev at distance</td><td style="text-align: right;;"></td><td style="text-align: right;;">931</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1047</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Test</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>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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">K38</th><td style="text-align:left">=MATCH(<font color="Blue">$K$36,Table15

#VALUE!

 

[rangerrambo]

I guess I just solve this with indirect function.

 

[p45cal]

I can see your problem - quite a knotty one - and I don't have a definitive answer, however, not only do you want to do a MATCH on visible cells, but you'll also want the OFFSET to work on vsible cells too. I don't think this is going to happen.

My initial thoughts are that you may have to have multiple tables created from your main table. Certainly you could do this with Advanced Filtering, where you place the new table in a different location from the original table. Another possibility is to create several Pivot Tables each showing effectively filtered data, though I'm not sure how well MATCH and OFFSET would work in Pivots, I've never tried - I think they would work.

Creating new tables with advanced filtering could be quite neat as you'd only need to create two columns per table.

If the data rarely changes, you could filter the list and cut and paste to other location/sheets manually.

By the way, I see you used OFFSET from the first DATA row, rather than from the header row; what happens if that row is hidden by filtering?! I would have used an offset from the header row, it never disappears and you wouldn't have to subtract 1 from the Match result.

I'm going away for a couple of weeks and I may not have internet access to follow/contribute very much/at all during that time, but it's one that I'd be interested to see what solution(s) you get/try/finally succeed with!

 

[p45cal]

I guess I just solve this with indirect function.

More details?

 

[rangerrambo]

I went with the Pivot Table solution

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">distance</td><td style="text-align: right;;"></td><td style="text-align: right;;">1007</td></tr><tr ><td style="color: #161120;text-align: center;">37</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;">38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Elev at distance</td><td style="text-align: right;;"></td><td style="text-align: right;;">1331</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1333</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1000</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Test</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>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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">K36</th><td style="text-align:left">=SQRT(<font color="Blue">J34^2+J35^2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K38</th><td style="text-align:left">=MATCH(<font color="Blue">$K$36,D56:D204,1</font>)-1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K39</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">(<font color="Green">$K$36-(<font color="Purple">OFFSET(<font color="Teal">$D$56,K38,0</font>)</font>)</font>)/(<font color="Green">(<font color="Purple">OFFSET(<font color="Teal">$D$56,K38+1,0</font>)-(<font color="Teal">OFFSET(<font color="#FF00FF">$D$56,K38,0</font>)</font>)</font>)</font>)</font>)*(<font color="Red">(<font color="Green">OFFSET(<font color="Purple">$D$56,K38+1,1</font>)</font>)-(<font color="Green">OFFSET(<font color="Purple">$D$56,K38,1</font>)</font>)</font>)</font>)+OFFSET(<font color="Blue">$D$56,K38,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K40</th><td style="text-align:left">=OFFSET(<font color="Blue">$D$56,K38,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K41</th><td style="text-align:left">=OFFSET(<font color="Blue">$D$56,K38,0</font>)</td></tr></tbody></table></td></tr></table><br />


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">51</td><td style=";">Type A</td><td style=";">82mm</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;">52</td><td style=";">Type B</td><td style=";">HE/WP</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;">53</td><td style=";">Table</td><td style=";">MEDIUM</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;">54</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;">55</td><td style=";">Distance</td><td style=";">Elevation</td><td style=";">Eleva A-100</td><td style=";">T Time A-100</td><td style=";">Time OF</td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style=";">350</td><td style="text-align: right;;">1510</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.4</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style=";">400</td><td style="text-align: right;;">1497</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.4</td></tr><tr ><td style="color: #161120;text-align: center;">58</td><td style=";">450</td><td style="text-align: right;;">1484</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.3</td></tr><tr ><td style="color: #161120;text-align: center;">59</td><td style=";">500</td><td style="text-align: right;;">1471</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.3</td></tr><tr ><td style="color: #161120;text-align: center;">60</td><td style=";">550</td><td style="text-align: right;;">1458</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.3</td></tr><tr ><td style="color: #161120;text-align: center;">61</td><td style=";">600</td><td style="text-align: right;;">1445</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.2</td></tr><tr ><td style="color: #161120;text-align: center;">62</td><td style=";">650</td><td style="text-align: right;;">1431</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.1</td></tr><tr ><td style="color: #161120;text-align: center;">63</td><td style=";">700</td><td style="text-align: right;;">1418</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28.1</td></tr><tr ><td style="color: #161120;text-align: center;">64</td><td style=";">750</td><td style="text-align: right;;">1404</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">28</td></tr><tr ><td style="color: #161120;text-align: center;">65</td><td style=";">800</td><td style="text-align: right;;">1390</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.7</td><td style="text-align: right;;">27.9</td></tr><tr ><td style="color: #161120;text-align: center;">66</td><td style=";">850</td><td style="text-align: right;;">1376</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.8</td><td style="text-align: right;;">27.8</td></tr><tr ><td style="color: #161120;text-align: center;">67</td><td style=";">900</td><td style="text-align: right;;">1362</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.8</td><td style="text-align: right;;">27.8</td></tr></tbody></table><p style="width:6.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Test</p><br /><br />