Find something in column A then show the value of B for that row in Excel 2010

Question

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.

I will give a basic example:

Title      Answer
Police       15
Ambulance    20
Fire         89

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).

I cant just say IF(A2="Police";B2;"" as I need the scan the whole of the Title column.

I have tried using IF(COUNTIF(A$2:A$100;"Police"); 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 COUNTIF found Police in cell A44 then the answer to my formula would be B44, the same as if it found Police in A62 then my formula should show B62

I hope this makes sense and that someone can help me :)

Note that I am using excel 2010 and need a normal formula as I can not use scripting for this document.

EDIT:

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...

=IF(ISNUMBER(FIND("RuhrP";F9));LOOKUP(A9;Ruhrpumpen!A$5:A$100;Ruhrpumpen!I$5:I$100);"")

This is showing "RuhrP" in every answer where "RuhrP" is found in F9 and not the answer I want which should be that found in RuhrPumpen!I$5:I$100 where the cell index is the same as that for the A coloum where A9 was found. Again, sorry for the complexity I cant think of any better way to word it.

Answer 1

I note you suggested this formula

=IF(ISNUMBER(FIND("RuhrP";F9));LOOKUP(A9;Ruhrpumpen!A$5:A$100;Ruhrpumpen!I$5:I$100);"")

.....but LOOKUP isn't appropriate here because I assume you want an exact match (LOOKUP won't guarantee that and also data in lookup range has to be sorted), so VLOOKUP or INDEX/MATCH would be better....and you can also use IFERROR to avoid the IF function, i.e

=IFERROR(VLOOKUP(A9;Ruhrpumpen!A$5:Z$100;9;0);"")

Note: VLOOKUP always looks up the lookup value (A9) in the first column of the "table array" and returns a value from the nth column of the "table array" where n is defined by col_index_num, in this case 9

INDEX/MATCH is sometimes more flexible because you can explicitly define the lookup column and the return column (and return column can be to the left of the lookup column which can't be the case in VLOOKUP), so that would look like this:

=IFERROR(INDEX(Ruhrpumpen!I$5:I$100;MATCH(A9;Ruhrpumpen!A$5:A$100;0));"")

INDEX/MATCH also allows you to more easily return multiple values from different columns, e.g. by using $ signs in front of A9 and the lookup range Ruhrpumpen!A$5:A$100, i.e.

=IFERROR(INDEX(Ruhrpumpen!I$5:I$100;MATCH($A9;Ruhrpumpen!$A$5:$A$100;0));"")

this version can be dragged across to get successive values from column I, column J, column K etc.....

Answer 2

Assuming

source data range is A1:B100.
query cell is D1 (here you will input Police or Fire).
result cell is E1

Formula in E1 = VLOOKUP(D1, A1:B100, 2, FALSE)