Vlookup and match function

[Asu]

Hi there, I have a table as a source.

Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">Stt</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">Loc</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">RI</td><td style="text-align: center; border-top: 1px solid black; border-bottom: 1px solid black; border-left: 1px solid black; background-color: rgb(253, 233, 217);">Coe</td><td style="text-align: center; border-top: 1px solid black; border-bottom: 1px solid black; background-color: rgb(253, 233, 217);">
</td><td style="text-align: center; border-top: 1px solid black; border-bottom: 1px solid black; background-color: rgb(253, 233, 217);">
</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black; border-bottom: 1px solid black; background-color: rgb(253, 233, 217);">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(StT)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(Loc)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(Yr)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">a</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">b</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">c</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">d</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: center; border-top: 1px solid black; border-left: 1px solid black;">K</td><td style="text-align: center; border-top: 1px solid black;">AS</td><td style="text-align: center; border-top: 1px solid black;">2</td><td style="text-align: center; border-top: 1px solid black;">5.6790</td><td style="text-align: center; border-top: 1px solid black;">-0.0276</td><td style="text-align: center; border-top: 1px solid black;">-0.0993</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black;">0.0033</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">5</td><td style="text-align: center;">4.9709</td><td style="text-align: center;">0.5460</td><td style="text-align: center;">-0.2176</td><td style="text-align: center; border-right: 1px solid black;">0.0113</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">5.6422</td><td style="text-align: center;">0.1575</td><td style="text-align: center;">-0.1329</td><td style="text-align: center; border-right: 1px solid black;">0.0056</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">20</td><td style="text-align: center;">5.8203</td><td style="text-align: center;">0.1093</td><td style="text-align: center;">-0.1248</td><td style="text-align: center; border-right: 1px solid black;">0.0053</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">50</td><td style="text-align: center;">5.7420</td><td style="text-align: center;">0.2273</td><td style="text-align: center;">-0.1481</td><td style="text-align: center; border-right: 1px solid black;">0.0068</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="text-align: center; border-bottom: 1px solid black; border-left: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">100</td><td style="text-align: center; border-bottom: 1px solid black;">6.3202</td><td style="text-align: center; border-bottom: 1px solid black;">-0.0778</td><td style="text-align: center; border-bottom: 1px solid black;">-0.0849</td><td style="text-align: center; border-right: 1px solid black; border-bottom: 1px solid black;">0.0026</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="text-align: center; border-top: 1px solid black; border-left: 1px solid black;">F</td><td style="text-align: center; border-top: 1px solid black;">KLR</td><td style="text-align: center; border-top: 1px solid black;">2</td><td style="text-align: center; border-top: 1px solid black;">5.3255</td><td style="text-align: center; border-top: 1px solid black;">0.1806</td><td style="text-align: center; border-top: 1px solid black;">-0.1322</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black;">0.0047</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">5</td><td style="text-align: center;">5.1086</td><td style="text-align: center;">0.5037</td><td style="text-align: center;">-0.2155</td><td style="text-align: center; border-right: 1px solid black;">0.0112</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">4.9696</td><td style="text-align: center;">0.6796</td><td style="text-align: center;">-0.2584</td><td style="text-align: center; border-right: 1px solid black;">0.0147</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">20</td><td style="text-align: center;">4.9781</td><td style="text-align: center;">0.7533</td><td style="text-align: center;">-0.2796</td><td style="text-align: center; border-right: 1px solid black;">0.0166</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">50</td><td style="text-align: center;">4.8047</td><td style="text-align: center;">0.9399</td><td style="text-align: center;">-0.3218</td><td style="text-align: center; border-right: 1px solid black;">0.0197</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="text-align: center; border-bottom: 1px solid black; border-left: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">100</td><td style="text-align: center; border-bottom: 1px solid black;">5.0064</td><td style="text-align: center; border-bottom: 1px solid black;">0.8709</td><td style="text-align: center; border-bottom: 1px solid black;">-0.3070</td><td style="text-align: center; border-right: 1px solid black; border-bottom: 1px solid black;">0.0186</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="text-align: center; border-top: 1px solid black; border-left: 1px solid black;">M</td><td style="text-align: center; border-top: 1px solid black;">MA</td><td style="text-align: center; border-top: 1px solid black;">2</td><td style="text-align: center; border-top: 1px solid black;">3.7091</td><td style="text-align: center; border-top: 1px solid black;">1.1622</td><td style="text-align: center; border-top: 1px solid black;">-0.0329</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black;">0.0176</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">5</td><td style="text-align: center;">4.3987</td><td style="text-align: center;">0.7725</td><td style="text-align: center;">-0.2381</td><td style="text-align: center; border-right: 1px solid black;">0.0112</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">17</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">4.9930</td><td style="text-align: center;">0.4661</td><td style="text-align: center;">-0.1740</td><td style="text-align: center; border-right: 1px solid black;">0.0069</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">18</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">20</td><td style="text-align: center;">5.0856</td><td style="text-align: center;">0.5048</td><td style="text-align: center;">-0.1875</td><td style="text-align: center; border-right: 1px solid black;">0.0082</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">19</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">50</td><td style="text-align: center;">4.8506</td><td style="text-align: center;">0.7398</td><td style="text-align: center;">-0.2388</td><td style="text-align: center; border-right: 1px solid black;">0.0117</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">20</td><td style="text-align: center; border-bottom: 1px solid black; border-left: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">100</td><td style="text-align: center; border-bottom: 1px solid black;">5.3796</td><td style="text-align: center; border-bottom: 1px solid black;">0.4628</td><td style="text-align: center; border-bottom: 1px solid black;">-0.1826</td><td style="text-align: center; border-right: 1px solid black; border-bottom: 1px solid black;">0.0081</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">21</td><td style="text-align: center; border-top: 1px solid black; border-left: 1px solid black;">PP</td><td style="text-align: center; border-top: 1px solid black;">P</td><td style="text-align: center; border-top: 1px solid black;">2</td><td style="text-align: center; border-top: 1px solid black;">4.5140</td><td style="text-align: center; border-top: 1px solid black;">0.6729</td><td style="text-align: center; border-top: 1px solid black;">-0.2311</td><td style="text-align: center; border-top: 1px solid black; border-right: 1px solid black;">0.0118</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">22</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">5</td><td style="text-align: center;">3.9599</td><td style="text-align: center;">1.1284</td><td style="text-align: center;">-0.3240</td><td style="text-align: center; border-right: 1px solid black;">0.0180</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">23</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">10</td><td style="text-align: center;">3.7277</td><td style="text-align: center;">1.4393</td><td style="text-align: center;">-0.4023</td><td style="text-align: center; border-right: 1px solid black;">0.0241</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">24</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">20</td><td style="text-align: center;">3.3255</td><td style="text-align: center;">1.7689</td><td style="text-align: center;">-0.4703</td><td style="text-align: center; border-right: 1px solid black;">0.0286</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">25</td><td style="text-align: center; border-left: 1px solid black;">
</td><td style="text-align: center;">
</td><td style="text-align: center;">50</td><td style="text-align: center;">2.8429</td><td style="text-align: center;">2.1456</td><td style="text-align: center;">-0.5469</td><td style="text-align: center; border-right: 1px solid black;">0.0335</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">26</td><td style="text-align: center; border-bottom: 1px solid black; border-left: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">
</td><td style="text-align: center; border-bottom: 1px solid black;">100</td><td style="text-align: center; border-bottom: 1px solid black;">2.7512</td><td style="text-align: center; border-bottom: 1px solid black;">2.2417</td><td style="text-align: center; border-bottom: 1px solid black;">-0.5610</td><td style="text-align: center; border-right: 1px solid black; border-bottom: 1px solid black;">0.0341</td></tr></tbody></table>

IDF Coeff (2)​

And I want to make a table to select one of items in the table only. For example,<o></o>
Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(StT)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(Loc)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(Yr)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">a</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">b</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">c</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">d</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: center; border: 1px solid black;">K</td><td style="text-align: center; border: 1px solid black;">AS</td><td style="text-align: center; border: 1px solid black;">10</td><td style="text-align: center; border: 1px solid black;">5.6422</td><td style="text-align: center; border: 1px solid black;">0.1575</td><td style="text-align: center; border: 1px solid black;">-0.1329</td><td style="text-align: center; border: 1px solid black;">0.0056</td></tr></tbody></table>

IDF Coeff (2)​

In this table I can make up to (Yr) only. Can someone guide me how to make it? Basically, in this table, I will need to make options on (StT) and (Yr) will do. It will come out for the rest.<o></o>

Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">31</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(StT)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(252, 213, 180);">(Loc)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">(Yr)</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">a</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">b</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">c</td><td style="text-align: center; border: 1px solid black; background-color: rgb(253, 233, 217);">d</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">32</td><td style="text-align: center; border: 1px solid black;">K</td><td style="text-align: center; border: 1px solid black;">AS</td><td style="text-align: center; border: 1px solid black;">10</td><td style="text-align: center; border: 1px solid black;">#VALUE!</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td><td style="text-align: center; border: 1px solid black;">
</td></tr></tbody></table>

IDF Coeff (2)​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" cellpadding="2.5px" width="85%" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(187, 187, 187); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" cellpadding="2.5px" width="100%" rules="all"><thead><tr style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);"><th width="10px">Cell</th><th style="text-align: left; padding-left: 5px;">Formula</th></tr></thead><tbody><tr><th style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);" width="10px">F32</th><td style="text-align: left;">=VLOOKUP($E32,Source!C3:D6,2,0)</td></tr><tr><th style="background-color: rgb(218, 231, 245); color: rgb(22, 17, 32);" width="10px">H32</th><td style="text-align: left;">=VLOOKUP($F32:$G32,$C$3:$G$26,MATCH($H31,$C$2:$G$2,0),0)</td></tr></tbody></table></td></tr></tbody></table>




<o></o>

 

[barry houdini]

Assuming the D3:D8 values always repeat in each section try this formula in M3 copied across to P3

=INDEX(E3:E26,MATCH(1,INDEX(($B3:$B26=$J3)*($C3:$C26=$K3),0),0)+MATCH($L3,$D3:$D8,0)-1)

 

[Asu]

sorry, barry.
i did not mention that E32 and G32 are in drop-down list function.
according to the way you showed, you assumed the E32 & G32 is typed in?
i copied the method you showed, it ends up with:

<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 /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">(StT)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">(Loc)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">(Yr)</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">a</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">b</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">c</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FCD5B4;;">d</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">K</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">AS</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">#N/A</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:7.8em;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">IDF Coeff (2)</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">M3</th><td style="text-align:left">=INDEX(<font color="Blue">E3:E26,MATCH(<font color="Red">1,INDEX(<font color="Green">(<font color="Purple">$B3:$B26=$J3</font>)*(<font color="Purple">$C3:$C26=$K3</font>),0</font>),0</font>)+MATCH(<font color="Red">$L3,$D3:$D8,0</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />

did i paste in the wrong cell?

 

[Asu]

did i make a mistake?

 

[Aladin Akyurek]

did i make a mistake?

What results do you expect for the

F, KLR, 7

triple?

 

[Asu]

Hi Aladin,
There will not be a 7. There are only 2,5,10,20,50,100.
These are the coefficient base on 2,5,10,20,50,100.
Basically, we do not use the number apart from 2,5,10,20,50,100.

 

[Asu]

the huge table i show on the top is from a code. there are fixed.

 

[Haseeb Avarakkan]

Hello Asu,

Just change the Barry Houdini's formula to;

M3, copy across.

=INDEX(D3:D26,MATCH(1,INDEX(($A3:$A26=$J3)*($B3:$B26=$K3),0),0)+MATCH($L3,$C3:$C8,0)-1)

 

[Asu]

thank you Haseeb.
it is done.
now i want to study how to apply that formulas. thank you.