VLOOKUP or HLOOKUP a single value in multiple columns

shinger

New Member
Joined
Jan 22, 2012
Messages
2
Greetings, this'll be my first post here although I must admit I have been frequenting this site and lurking for answers (usually I find them) for years.

I'm trying to do something with HLOOKUP and VLOOKUP which seems to me should be easy, so much so that I'm wondering if I'm using the right function at all. I've been searching for this answer now for about 3 hours straight and everyone who mentions a similar question is usually trying to do something more complicated than I am with the results, so I have high hopes!

This is using Windows XP, with Excel 2003. Here's the nutshell:

All the data in my table, except for the boldface headings, is unique. There are no duplicates. I would like to have 2 working result functions, as in the example below:
Result in ZZ1: Find the value 157 wherever it occurs in the whole table, and return the corresponding "Food Type" from the right-hand side in the same "row".
Result in ZZ2: Find the value 157 again, and this time return the corresponding "Number" from the bottom row in the same "column".

So in essence, if I pick a # and search for it, even if it's not in the left-most column, it should find the corresponding "Number" and "Food Type" from the heading sections, and I specifically want it to return the values separately as separate functions populating separate cells.

By the time I'm done with the sheet, I'll likely have done a search about 6 times for every number existing in this table. Every known combination of "Food Type" and "Number" will exist in the result cells, probably half a dozen times at least.

Thank you in advance :)

<b>Array 1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:40px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:12pt; text-align:center; ">1</td><td style="font-size:12pt; text-align:center; ">2</td><td style="font-size:12pt; text-align:center; ">3</td><td style="font-size:12pt; text-align:center; ">4</td><td style="font-size:12pt; text-align:center; ">5</td><td style="font-size:12pt; text-align:center; ">6</td><td style="font-size:12pt; text-align:center; ">7</td><td style="font-size:12pt; text-align:center; ">8</td><td style="font-size:12pt; text-align:center; ">9</td><td style="font-size:12pt; text-align:center; ">10</td><td style="font-size:12pt; text-align:center; ">11</td><td style="font-size:12pt; text-align:center; ">12</td><td style="font-size:12pt; text-align:center; ">13</td><td style="font-size:12pt; text-align:center; ">14</td><td style="font-size:12pt; text-align:center; ">15</td><td style="font-size:12pt; text-align:center; ">16</td><td style="font-size:12pt; text-align:center; ">17</td><td style="font-size:12pt; text-align:center; ">18</td><td style="font-size:12pt; text-align:center; ">19</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">APPLE</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:12pt; text-align:center; ">20</td><td style="font-size:12pt; text-align:center; ">21</td><td style="font-size:12pt; text-align:center; ">22</td><td style="font-size:12pt; text-align:center; ">23</td><td style="font-size:12pt; text-align:center; ">24</td><td style="font-size:12pt; text-align:center; ">25</td><td style="font-size:12pt; text-align:center; ">26</td><td style="font-size:12pt; text-align:center; ">27</td><td style="font-size:12pt; text-align:center; ">28</td><td style="font-size:12pt; text-align:center; ">29</td><td style="font-size:12pt; text-align:center; ">30</td><td style="font-size:12pt; text-align:center; ">31</td><td style="font-size:12pt; text-align:center; ">32</td><td style="font-size:12pt; text-align:center; ">33</td><td style="font-size:12pt; text-align:center; ">34</td><td style="font-size:12pt; text-align:center; ">35</td><td style="font-size:12pt; text-align:center; ">36</td><td style="font-size:12pt; text-align:center; ">37</td><td style="font-size:12pt; text-align:center; ">38</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">ORANGE</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:12pt; text-align:center; ">39</td><td style="font-size:12pt; text-align:center; ">40</td><td style="font-size:12pt; text-align:center; ">41</td><td style="font-size:12pt; text-align:center; ">42</td><td style="font-size:12pt; text-align:center; ">43</td><td style="font-size:12pt; text-align:center; ">44</td><td style="font-size:12pt; text-align:center; ">45</td><td style="font-size:12pt; text-align:center; ">46</td><td style="font-size:12pt; text-align:center; ">47</td><td style="font-size:12pt; text-align:center; ">48</td><td style="font-size:12pt; text-align:center; ">49</td><td style="font-size:12pt; text-align:center; ">50</td><td style="font-size:12pt; text-align:center; ">51</td><td style="font-size:12pt; text-align:center; ">52</td><td style="font-size:12pt; text-align:center; ">53</td><td style="font-size:12pt; text-align:center; ">54</td><td style="font-size:12pt; text-align:center; ">55</td><td style="font-size:12pt; text-align:center; ">56</td><td style="font-size:12pt; text-align:center; ">57</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">PEAR</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:12pt; text-align:center; ">58</td><td style="font-size:12pt; text-align:center; ">59</td><td style="font-size:12pt; text-align:center; ">60</td><td style="font-size:12pt; text-align:center; ">61</td><td style="font-size:12pt; text-align:center; ">62</td><td style="font-size:12pt; text-align:center; ">63</td><td style="font-size:12pt; text-align:center; ">64</td><td style="font-size:12pt; text-align:center; ">65</td><td style="font-size:12pt; text-align:center; ">66</td><td style="font-size:12pt; text-align:center; ">67</td><td style="font-size:12pt; text-align:center; ">68</td><td style="font-size:12pt; text-align:center; ">69</td><td style="font-size:12pt; text-align:center; ">70</td><td style="font-size:12pt; text-align:center; ">71</td><td style="font-size:12pt; text-align:center; ">72</td><td style="font-size:12pt; text-align:center; ">73</td><td style="font-size:12pt; text-align:center; ">74</td><td style="font-size:12pt; text-align:center; ">75</td><td style="font-size:12pt; text-align:center; ">76</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">PEACH</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:12pt; text-align:center; ">77</td><td style="font-size:12pt; text-align:center; ">78</td><td style="font-size:12pt; text-align:center; ">79</td><td style="font-size:12pt; text-align:center; ">80</td><td style="font-size:12pt; text-align:center; ">81</td><td style="font-size:12pt; text-align:center; ">82</td><td style="font-size:12pt; text-align:center; ">83</td><td style="font-size:12pt; text-align:center; ">84</td><td style="font-size:12pt; text-align:center; ">85</td><td style="font-size:12pt; text-align:center; ">86</td><td style="font-size:12pt; text-align:center; ">87</td><td style="font-size:12pt; text-align:center; ">88</td><td style="font-size:12pt; text-align:center; ">89</td><td style="font-size:12pt; text-align:center; ">90</td><td style="font-size:12pt; text-align:center; ">91</td><td style="font-size:12pt; text-align:center; ">92</td><td style="font-size:12pt; text-align:center; ">93</td><td style="font-size:12pt; text-align:center; ">94</td><td style="font-size:12pt; text-align:center; ">95</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">CHERRY</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:12pt; text-align:center; ">96</td><td style="font-size:12pt; text-align:center; ">97</td><td style="font-size:12pt; text-align:center; ">98</td><td style="font-size:12pt; text-align:center; ">99</td><td style="font-size:12pt; text-align:center; ">100</td><td style="font-size:12pt; text-align:center; ">101</td><td style="font-size:12pt; text-align:center; ">102</td><td style="font-size:12pt; text-align:center; ">103</td><td style="font-size:12pt; text-align:center; ">104</td><td style="font-size:12pt; text-align:center; ">105</td><td style="font-size:12pt; text-align:center; ">106</td><td style="font-size:12pt; text-align:center; ">107</td><td style="font-size:12pt; text-align:center; ">108</td><td style="font-size:12pt; text-align:center; ">109</td><td style="font-size:12pt; text-align:center; ">110</td><td style="font-size:12pt; text-align:center; ">111</td><td style="font-size:12pt; text-align:center; ">112</td><td style="font-size:12pt; text-align:center; ">113</td><td style="font-size:12pt; text-align:center; ">114</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">BANANA</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:12pt; text-align:center; ">115</td><td style="font-size:12pt; text-align:center; ">116</td><td style="font-size:12pt; text-align:center; ">117</td><td style="font-size:12pt; text-align:center; ">118</td><td style="font-size:12pt; text-align:center; ">119</td><td style="font-size:12pt; text-align:center; ">120</td><td style="font-size:12pt; text-align:center; ">121</td><td style="font-size:12pt; text-align:center; ">122</td><td style="font-size:12pt; text-align:center; ">123</td><td style="font-size:12pt; text-align:center; ">124</td><td style="font-size:12pt; text-align:center; ">125</td><td style="font-size:12pt; text-align:center; ">126</td><td style="font-size:12pt; text-align:center; ">127</td><td style="font-size:12pt; text-align:center; ">128</td><td style="font-size:12pt; text-align:center; ">129</td><td style="font-size:12pt; text-align:center; ">130</td><td style="font-size:12pt; text-align:center; ">131</td><td style="font-size:12pt; text-align:center; ">132</td><td style="font-size:12pt; text-align:center; ">133</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">MANGO</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:12pt; text-align:center; ">134</td><td style="font-size:12pt; text-align:center; ">135</td><td style="font-size:12pt; text-align:center; ">136</td><td style="font-size:12pt; text-align:center; ">137</td><td style="font-size:12pt; text-align:center; ">138</td><td style="font-size:12pt; text-align:center; ">139</td><td style="font-size:12pt; text-align:center; ">140</td><td style="font-size:12pt; text-align:center; ">141</td><td style="font-size:12pt; text-align:center; ">142</td><td style="font-size:12pt; text-align:center; ">143</td><td style="font-size:12pt; text-align:center; ">144</td><td style="font-size:12pt; text-align:center; ">145</td><td style="font-size:12pt; text-align:center; ">146</td><td style="font-size:12pt; text-align:center; ">147</td><td style="font-size:12pt; text-align:center; ">148</td><td style="font-size:12pt; text-align:center; ">149</td><td style="font-size:12pt; text-align:center; ">150</td><td style="font-size:12pt; text-align:center; ">151</td><td style="font-size:12pt; text-align:center; ">152</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">CELERY</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:12pt; text-align:center; ">153</td><td style="font-size:12pt; text-align:center; ">154</td><td style="font-size:12pt; text-align:center; ">155</td><td style="font-size:12pt; text-align:center; ">156</td><td style="font-size:12pt; text-align:center; ">157</td><td style="font-size:12pt; text-align:center; ">158</td><td style="font-size:12pt; text-align:center; ">159</td><td style="font-size:12pt; text-align:center; ">160</td><td style="font-size:12pt; text-align:center; ">161</td><td style="font-size:12pt; text-align:center; ">162</td><td style="font-size:12pt; text-align:center; ">163</td><td style="font-size:12pt; text-align:center; ">164</td><td style="font-size:12pt; text-align:center; ">165</td><td style="font-size:12pt; text-align:center; ">166</td><td style="font-size:12pt; text-align:center; ">167</td><td style="font-size:12pt; text-align:center; ">168</td><td style="font-size:12pt; text-align:center; ">169</td><td style="font-size:12pt; text-align:center; ">170</td><td style="font-size:12pt; text-align:center; ">171</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">CARROT</td></tr><tr style="height:48px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">1</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">2</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">3</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">4</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">5</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">6</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">7</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">8</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">9</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">10</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">11</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">12</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">13</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">14</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">15</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">16</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">17</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">18</td><td style="font-weight:bold; font-size:14pt; text-align:center; ">19</td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
http://www.excel-jeanie-html.de/index.php?f=1
 
Which is better Vlookup or Hlookup to approach end result.

[TABLE="width: 1263"]
<tbody>[TR]
[TD="colspan: 5, align: center"]DOCUMENTS [/TD]
[TD]Policy Manual[/TD]
[TD]Document Control[/TD]
[TD]Control of Records[/TD]
[TD]Engineering Change[/TD]
[TD]Management review[/TD]
[TD]Training and Qualification[/TD]
[TD]Work Environment[/TD]
[TD]Project Controls[/TD]
[TD]Validation of Processes[/TD]
[TD]Customer Orders[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Function[/TD]
[TD]First Name[/TD]
[TD]Last
Name[/TD]
[TD]Reports to[/TD]
[TD]Position[/TD]
[TD]MP- 01[/TD]
[TD]MP 2[/TD]
[TD]MP 3.0[/TD]
[TD]MP 4[/TD]
[TD]MP 5[/TD]
[TD]GP 6[/TD]
[TD]MP 7.2[/TD]
[TD]MP 1-1[/TD]
[TD]MP 7.1-2[/TD]
[TD]MP 7.2-1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Revision[/TD]
[TD]F[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LMT[/TD]
[TD]John[/TD]
[TD]Smith1[/TD]
[TD][/TD]
[TD]President & CEO[/TD]
[TD]REV[/TD]
[TD]Done[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]Started[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Done[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith2[/TD]
[TD][/TD]
[TD]Manufacturing (MR)[/TD]
[TD]REV[/TD]
[TD]REV[/TD]
[TD]REV[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]Done[/TD]
[TD]Started[/TD]
[TD][/TD]
[TD]Done[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith3[/TD]
[TD][/TD]
[TD]Development International[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]REV[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]Done[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith4[/TD]
[TD][/TD]
[TD]Technology[/TD]
[TD]REV[/TD]
[TD]Done[/TD]
[TD]REV[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]Started[/TD]
[TD][/TD]
[TD][/TD]
[TD]REV[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith5[/TD]
[TD][/TD]
[TD]Regulatory and Health Policy[/TD]
[TD]REV[/TD]
[TD]Done[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]REV[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith6[/TD]
[TD][/TD]
[TD]General Manager[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]Started[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Done[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Smith7[/TD]
[TD][/TD]
[TD]Financial Operations[/TD]
[TD]REV[/TD]
[TD]Started[/TD]
[TD]REV[/TD]
[TD][/TD]
[TD]Started[/TD]
[TD]Started[/TD]
[TD][/TD]
[TD]Started[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'm working on final view as below, any suggestion are appreciated.

Searching Johm smith6 :

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]policy manual[/TD]
[TD]MP 01 | F [/TD]
[/TR]
[TR]
[TD]Document Control[/TD]
[TD]MP 2 | B[/TD]
[/TR]
[TR]
[TD]Engineering Change[/TD]
[TD]MP 4 | F[/TD]
[/TR]
[TR]
[TD]Management review [/TD]
[TD]MP 5 | C[/TD]
[/TR]
[TR]
[TD]Work Environment [/TD]
[TD]MP 7.2 |B[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

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
Back
Top