title: Find something in column A then show the value of B for that row in Excel 2010
body: <p>Basically my problem is that I have a string in one cell in excel, I then need to see if that string exists in another row (not one cell but the whole row) and if so then print the contents of another cell in the same row but in another column. </p>
  
  <p>I will give a basic example:</p>
  
  <pre><code>Title      Answer
  Police       15
  Ambulance    20
  Fire         89
  </code></pre>
  
  <p>Now I need to scan the title column for, say, "Police" and then populate the cell with the value under Answer (in this case 15).</p>
  
  <p>I cant just say <code>IF(A2="Police";B2;""</code> as I need the scan the whole of the Title column.</p>
  
  <p>I have tried using <code>IF(COUNTIF(A$2:A$100;"Police");</code> which scans the contents of A2 to A100 for the string Police, and know how to make it print a constant (just put something after the ;) but cant work out how to make that "constant" a variable that changes depending on the found row. So if the <code>COUNTIF</code> found Police in cell <code>A44</code> then the answer to my formula would be <code>B44</code>, the same as if it found Police in <code>A62</code> then my formula should show <code>B62</code></p>
  
  <p>I hope this makes sense and that someone can help me :)</p>
  
  <p>Note that I am using excel 2010 and need a normal formula as I can not use scripting for this document.</p>
  
  <p>EDIT:</p>
  
  <p>Here is what I have so far, note that the spreadsheet I am using is far more complex than the "simple" example I have in the question...</p>
  
  <pre><code>=IF(ISNUMBER(FIND("RuhrP";F9));LOOKUP(A9;Ruhrpumpen!A$5:A$100;Ruhrpumpen!I$5:I$100);"")
  </code></pre>
  
  <p>This is showing <code>"RuhrP"</code> in every answer where <code>"RuhrP"</code> is found in <code>F9</code> and not the answer I want which should be that found in <code>RuhrPumpen!I$5:I$100</code> where the cell index is the same as that for the <code>A</code> coloum where <code>A9</code> was found. Again, sorry for the complexity I cant think of any better way to word it.</p>