VLOOKUP or HLOOKUP a single value in multiple columns

[shinger]

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

 

[Robert Mika]

Each number in table would return only one value-Intersection with Fodd Type and NUmber so howyou expect 6 results?

 

[Peter_SSs]

Welcome to the MrExcel board!

This seems to be what you are asking. If not please clarify.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	ZY	ZZ
1																						157	CARROT
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	APPLE			5
3	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	ORANGE
4	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	PEAR
5	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	PEACH
6	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	CHERRY
7	96	97	98	99	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	BANANA
8	115	116	117	118	119	120	121	122	123	124	125	126	127	128	129	130	131	132	133	MANGO
9	134	135	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150	151	152	CELERY
10	153	154	155	156	157	158	159	160	161	162	163	164	165	166	167	168	169	170	171	CARROT
11	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
Array 1

 

[Robert Mika]

Still not clear but this is my attempt:
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;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><th>S</th><th>T</th><th>ZY</th><th>ZZ</th><th>AAA</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">54</td><td style="text-align: center;;">PEAR</td><td style="text-align: center;;">Food Type</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;">14</td><td style="text-align: center;;">15</td><td style="text-align: center;;">16</td><td style="text-align: center;;">17</td><td style="text-align: center;;">18</td><td style="text-align: center;;">19</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;"></td><td style="text-align: center;;">16</td><td style="text-align: center;;">Number</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">20</td><td style="text-align: center;;">21</td><td style="text-align: center;;">22</td><td style="text-align: center;;">23</td><td style="text-align: center;;">24</td><td style="text-align: center;;">25</td><td style="text-align: center;;">26</td><td style="text-align: center;;">27</td><td style="text-align: center;;">28</td><td style="text-align: center;;">29</td><td style="text-align: center;;">30</td><td style="text-align: center;;">31</td><td style="text-align: center;;">32</td><td style="text-align: center;;">33</td><td style="text-align: center;;">34</td><td style="text-align: center;;">35</td><td style="text-align: center;;">36</td><td style="text-align: center;;">37</td><td style="text-align: center;;">38</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">39</td><td style="text-align: center;;">40</td><td style="text-align: center;;">41</td><td style="text-align: center;;">42</td><td style="text-align: center;;">43</td><td style="text-align: center;;">44</td><td style="text-align: center;;">45</td><td style="text-align: center;;">46</td><td style="text-align: center;;">47</td><td style="text-align: center;;">48</td><td style="text-align: center;;">49</td><td style="text-align: center;;">50</td><td style="text-align: center;;">51</td><td style="text-align: center;;">52</td><td style="text-align: center;;">53</td><td style="text-align: center;;">54</td><td style="text-align: center;;">55</td><td style="text-align: center;;">56</td><td style="text-align: center;;">57</td><td style="text-align: center;;">PEAR</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">58</td><td style="text-align: center;;">59</td><td style="text-align: center;;">60</td><td style="text-align: center;;">61</td><td style="text-align: center;;">62</td><td style="text-align: center;;">63</td><td style="text-align: center;;">64</td><td style="text-align: center;;">65</td><td style="text-align: center;;">66</td><td style="text-align: center;;">67</td><td style="text-align: center;;">68</td><td style="text-align: center;;">69</td><td style="text-align: center;;">70</td><td style="text-align: center;;">71</td><td style="text-align: center;;">72</td><td style="text-align: center;;">73</td><td style="text-align: center;;">74</td><td style="text-align: center;;">75</td><td style="text-align: center;;">76</td><td style="text-align: center;;">PEACH</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">77</td><td style="text-align: center;;">78</td><td style="text-align: center;;">79</td><td style="text-align: center;;">80</td><td style="text-align: center;;">81</td><td style="text-align: center;;">82</td><td style="text-align: center;;">83</td><td style="text-align: center;;">84</td><td style="text-align: center;;">85</td><td style="text-align: center;;">86</td><td style="text-align: center;;">87</td><td style="text-align: center;;">88</td><td style="text-align: center;;">89</td><td style="text-align: center;;">90</td><td style="text-align: center;;">91</td><td style="text-align: center;;">92</td><td style="text-align: center;;">93</td><td style="text-align: center;;">94</td><td style="text-align: center;;">95</td><td style="text-align: center;;">CHERRY</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">96</td><td style="text-align: center;;">97</td><td style="text-align: center;;">98</td><td style="text-align: center;;">99</td><td style="text-align: center;;">100</td><td style="text-align: center;;">101</td><td style="text-align: center;;">102</td><td style="text-align: center;;">103</td><td style="text-align: center;;">104</td><td style="text-align: center;;">105</td><td style="text-align: center;;">106</td><td style="text-align: center;;">107</td><td style="text-align: center;;">108</td><td style="text-align: center;;">109</td><td style="text-align: center;;">110</td><td style="text-align: center;;">111</td><td style="text-align: center;;">112</td><td style="text-align: center;;">113</td><td style="text-align: center;;">114</td><td style="text-align: center;;">BANANA</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">115</td><td style="text-align: center;;">116</td><td style="text-align: center;;">117</td><td style="text-align: center;;">118</td><td style="text-align: center;;">119</td><td style="text-align: center;;">120</td><td style="text-align: center;;">121</td><td style="text-align: center;;">122</td><td style="text-align: center;;">123</td><td style="text-align: center;;">124</td><td style="text-align: center;;">125</td><td style="text-align: center;;">126</td><td style="text-align: center;;">127</td><td style="text-align: center;;">128</td><td style="text-align: center;;">129</td><td style="text-align: center;;">130</td><td style="text-align: center;;">131</td><td style="text-align: center;;">132</td><td style="text-align: center;;">133</td><td style="text-align: center;;">MANGO</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">134</td><td style="text-align: center;;">135</td><td style="text-align: center;;">136</td><td style="text-align: center;;">137</td><td style="text-align: center;;">138</td><td style="text-align: center;;">139</td><td style="text-align: center;;">140</td><td style="text-align: center;;">141</td><td style="text-align: center;;">142</td><td style="text-align: center;;">143</td><td style="text-align: center;;">144</td><td style="text-align: center;;">145</td><td style="text-align: center;;">146</td><td style="text-align: center;;">147</td><td style="text-align: center;;">148</td><td style="text-align: center;;">149</td><td style="text-align: center;;">150</td><td style="text-align: center;;">151</td><td style="text-align: center;;">152</td><td style="text-align: center;;">CELERY</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">153</td><td style="text-align: center;;">154</td><td style="text-align: center;;">155</td><td style="text-align: center;;">156</td><td style="text-align: center;;">157</td><td style="text-align: center;;">158</td><td style="text-align: center;;">159</td><td style="text-align: center;;">160</td><td style="text-align: center;;">161</td><td style="text-align: center;;">162</td><td style="text-align: center;;">163</td><td style="text-align: center;;">164</td><td style="text-align: center;;">165</td><td style="text-align: center;;">166</td><td style="text-align: center;;">167</td><td style="text-align: center;;">168</td><td style="text-align: center;;">169</td><td style="text-align: center;;">170</td><td style="text-align: center;;">171</td><td style="text-align: center;;">CARROT</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">12</td><td style="text-align: center;;">13</td><td style="text-align: center;;">14</td><td style="text-align: center;;">15</td><td style="text-align: center;;">16</td><td style="text-align: center;;">17</td><td style="text-align: center;;">18</td><td style="text-align: center;;">19</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Array Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" width="100%" cellpadding="2.5px"><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 style=" background-color: #DAE7F5;color: #161120" width="10px">ZZ1</th><td style="text-align:left">{=INDEX($T$1:$T$10,MIN(IF(A1:S10=ZY1,ROW(A1:S10))))}</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">ZZ2</th><td style="text-align:left">{=INDEX(A11:S11,,MIN(IF(A2:S10=ZY1,COLUMN(A2:S10))))}</td></tr></tbody></table>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself</td></tr></tbody></table>

 

[Peter_SSs]

Robert

Note that those formulas as they stand are not robust against row and/or column insertion. Try entering a new Row 1 and/or Column A

 

[Kreszch68]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	APPLE	37	ORANGE
3	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	ORANGE		18
4	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57	PEAR
5	58	59	60	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75	76	PEACH
6	77	78	79	80	81	82	83	84	85	86	87	88	89	90	91	92	93	94	95	CHERRY
7	96	97	98	99	100	101	102	103	104	105	106	107	108	109	110	111	112	113	114	BANANA
8	115	116	117	118	119	120	121	122	123	124	125	126	127	128	129	130	131	132	133	MANGO
9	134	135	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150	151	152	CELERY
10	153	154	155	156	157	158	159	160	161	162	163	164	165	166	167	168	169	170	171	CARROT
11	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
Blad1

two UDF's

Public Function ReturnFoodType(ByVal SearchArray As Range, _
                               ByVal SearchValue As Long) As String
Dim vArray As Variant
Dim rL As Long
Dim cL As Long
vArray = SearchArray.Value
For rL = 1 To UBound(vArray, 1)
    For cL = 1 To UBound(vArray, 2)
        If SearchValue = vArray(rL, cL) Then GoTo Result
    Next cL
Next rL
ReturnFoodType = "Value not found"
Exit Function
Result:
ReturnFoodType = vArray(rL, UBound(vArray, 2))
End Function
Public Function ReturnNumber(ByVal SearchArray As Range, _
                             ByVal SearchValue As Long) As Variant
Dim vArray As Variant
Dim rL As Long
Dim cL As Long
vArray = SearchArray.Value
For rL = 1 To UBound(vArray, 1)
    For cL = 1 To UBound(vArray, 2)
        If SearchValue = vArray(rL, cL) Then GoTo Result
    Next cL
Next rL
ReturnNumber = "Value not found"
Exit Function
Result:
ReturnNumber = vArray(UBound(vArray, 1), cL)
End Function

 

[Robert Mika]

Robert

Note that those formulas as they stand are not robust against row and/or column insertion. Try entering a new Row 1 and/or Column A

Thank you Peter.
Another lesson learned.
What change woudl you suggest to keep the formula in existing format but still be prone to row/column change?

 

[Peter_SSs]

What change woudl you suggest to keep the formula in existing format but still be prone to row/column change?

I assume you mean not prone to row/column change. Look at the structure in the formulas I posted & try something like that.

=INDEX($T$2:$T$10,SUMPRODUCT(($A$2:$S$10=ZY1)*ROW($A$2:$S$10))-ROW($2:$2)+1)

 

[shinger]

=ReturnFoodType(A2:T11;U2)
=ReturnNumber(A2:T11;U2)

Thanks! I was able to use those 2 functions completely as-is, and have now incorporated them into a spreadsheet as the main data reference for a 180 employee matrix coordinating Areas & Focus Topics for safety inspections.

Thanks as well to all others who responded, as I learned about the INDEX function as well, though I didn't have an opportunity to use it in today's work.

Have a great day.

[SOLVED]

 

[Peter_SSs]

I learned about the INDEX function as well, though I didn't have an opportunity to use it in today's work.

I'm not aguing your choice to use the method that suits you best, but does the above indicate that the INDEX/SUMPRODUCT formulas produce results different to the UDFs (apart from the 'not found' circumstance)?


I note that the UDF's deal with the situation where the value being searched for does not appear in the grid. I'm not sure if that is possible with your data and I gather anyway that your real data is not like the sample posted.
However, I would recommend making these changes to each function since there is no point searching the final row or final column for something you only want to locate in rows/columns before that.

For rL = 1 To UBound(vArray, 1) - 1
    For cL = 1 To UBound(vArray, 2) - 1

This would also prevent the incorrect result being returned with your sample data if any number from 1 to 19 was being searched for and did not appear in the main part of the table.