I have a large spreadsheet with information that I need to copy over to a
newsheet. I need to pull location information out of a specific cell on the
first sheet and put it in a cell in a column on the second sheet in the row
that corresponds to the matching account number.

for example on sheet1 I have:

Column A Coulumn B
12345 New York

and on sheet2 I have:

Column A Column Z
12345

and in column Z I want to have Excel copy 'New York' from the first sheet.

This is what i have tried without luck:
=IF(COUNTIF(Sheet1!$B$2:$B$404,Sheet2!B6)0,Offset (MATCH(B6,Sheet1!$B$2:$B$404),0,11),"")

any help is greatly appreciated.