VLOOKUP formula issue - need to copy down rows w/o one value changing

[collinspat54]

I have an Excel 2007 spreadsheet with a worksheet “Verified” and worksheet6, that I will update based on information on SharePoint site being up to date. I have the formula working but if I copy it down the worksheet, the Value presented is #N/A. When copying the formula the values increase by a row number. The second value needs to remain “Verified!E2” and the end results are the correct value in Column K for the correct Row F value.</SPAN>

What I am trying to accomplish is:</SPAN>

in SHEET6 (this is a sheet that the values are replaced from a SharePpoint dump of data) look at the Cell in Column F</SPAN>
if there is a match anywhere in the Worksheet “Verified” Row E,</SPAN>
return the result of column K to “Sheet6” Row B (formula is in Row B)</SPAN>


In Worksheet6 I have the following formula working in row 2 =VLOOKUP(F2,Verified!E2:K1000,7,0)</SPAN>

If I copy the formula down the rows </SPAN>
the formula for row 7 is =VLOOKUP(F7,Verified!E7:K1005,7,0)</SPAN>
results in value of #N/A</SPAN>
if I edit the formula to =VLOOKUP(F7,Verified!E</SPAN>2</SPAN>:K1005,7,0)</SPAN> the results are the value in row 7 column K</SPAN>

How can I</SPAN>
1) copy the formula without having to edit the formula over 700 times or</SPAN>
2) use a different formula</SPAN>

 

[Andrew Poulsom]

Welcome to MrExcel.

Use $ signs to make the reference absolute when copying:

=VLOOKUP(F7,Verified!E$7:K$1005,7,0)</SPAN>

 

[collinspat54]

EXECELLENT - that worked. Thanks for the quick response