Vlookup does not copy/paste or drag

[sjuhl99]

I keep getting the #REF when i copy this vlookup down and have no reason why. Please help, the sheet and cells it is referencing is =IFERROR(VLOOKUP(A16,'Locked Forecasts-DNT'!A2:AI1048576,35,0),"-"), usually works when i manually type it but can't do it any other way. thanks


=IFERROR(VLOOKUP(A32,'Locked Forecasts-DNT'!#REF !,35,0),"-")

 

[alansidman]

Try this: =IFERROR(VLOOKUP(A16,'Locked Forecasts-DNT'!$A$2:$AI$1048576,35,0),"-")

look at this link on relative and absolute cell references to fully understand

https://www.gcflearnfree.org/excel2013/relative-and-absolute-cell-references/1/

 

[sjuhl99]

Thanks although you are suggesting absolute, I am trying to accomplish relative and getting the #ref , not using $.

 

[mikerickson]

The last cell in your relative reference is at the bottom of the sheet, drag that address down and there will be a #REF error.

The formula in the OP, what cell is it in?
When you copy it to a different cell, what cell and what do you want the formula to become?

 

[sjuhl99]

I think i know the issue, the source workbook is being filtered prior to the vlookup, it when copying and pasting it down its not consecutive, not sure but that might be the issue. I might have to do a large if/and statement instead of filtering.

 

[mikerickson]

Filtering the data base should have no effect on a VLOOKUP

The formula in the OP, what cell is it in?
When you copy it to a different cell, what cell and what do you want the formula to become?