title: Using index and match to do a lookup both vertically and horizontally
body: <p>I have one column of data on a sheet and each cell contains a value that could be found in an array on another sheet. I'm trying to find a function where if it finds the value in the array in sheet 2 from the column in sheet 1, then it returns the value in the leftmost column.</p>
  
  <p>Here's an example of the data:</p>
  
  <p><em>sheet 1</em></p>
  
  <pre><code>| AH2377507 |
  | 2188622   |
  | EA404278  |
  | AH885430  |
  </code></pre>
  
  <p><em>sheet 2</em></p>
  
  <pre><code>aaa | 1874379 | AH2377507 | EA2377507 | PS2377507 | 67004276 | 949529 | AH2065634
  bbb | 1938614 | 2186486   | 2188622   | 2188708
  ccc | 919973  | AH404278  | EA404278  | PS404278 | 2188746
  ddd | 960929  | AH885430  | EA885430  | PS885430
  </code></pre>
  
  <p>So when '2188622' is found in sheet 2, it returns 'bbb'.</p>
  
  <p>Also the number of columns with data in sheet 2 varies from row to row, so there are blank cells in the array, if that matters.</p>
  
  <p>I've tried nesting vlookp in hlookup and vise-versa as well as nesting hlookup in a index/match, but neither of those ideas worked.</p>