Vlookup help

[ERcoder]

Thanks to you guys I was able to learn this formula for my spreadsheet
=IF(ISNA(VLOOKUP(F2,AU1:AV33,2,FALSE)),"",VLOOKUP(F2,AU1:AV33,2,FALSE))

I used the auto copy function to click and drag the forumula to every cell in the column. As it copied down it changed the look up value (F2)as needed for each row but it also changed the rows in the table array (AU1:AV33) the same way.

How do I keep the table array the same as I copy down to the rest of the column. Or is there a different way to apply the formula to the whole column?

Thanks

 

[Jonmo1]

Try using Absolute references..
Look it up in the help files for a good explaination..

Try
=IF(ISNA(VLOOKUP(F2,AU$1:AV$33,2,FALSE)),"",VLOOKUP(F2,AU$1:AV$33,2,FALSE))

 

[VoG]

Try

=IF(ISNA(VLOOKUP(F2,AU$1:AV$33,2,FALSE)),"",VLOOKUP(F2,AU$1:AV$33,2,FALSE))

 

[ERcoder]

Thank you both.

 

[T. Valko]

Thanks to you guys I was able to learn this formula for my spreadsheet
=IF(ISNA(VLOOKUP(F2,AU1:AV33,2,FALSE)),"",VLOOKUP(F2,AU1:AV33,2,FALSE))

I used the auto copy function to click and drag the forumula to every cell in the column. As it copied down it changed the look up value (F2)as needed for each row but it also changed the rows in the table array (AU1:AV33) the same way.

How do I keep the table array the same as I copy down to the rest of the column. Or is there a different way to apply the formula to the whole column?

Thanks

If you're using Excel 2007 or later you can reduce that to:

=IFERROR(VLOOKUP(F2,AU$1:AV$33,2,0),"")

Note that IFERROR will trap ALL errors, not just #N/A errors.