Microsoft Excel | Inserting a Formula in Filtered Cells

I have some very large spreadsheets, in which information I need is spread across several. I have a master spreadsheet which is "pulling" information from those various sheets using VLOOKUP. However, everytime I go to change the Table Array in the formula, then drag the formula down it overwrites the cells I filter out because they have information in the them already. I have drawn up an example for simplicity sake, see below.

What I would do, is filter everything but the cells with #N/A and apply a new VLOOKUP to the next workbook, in an attempt to narrow down the list of items I don’t have information for. But when I drag from C5 to C8 the formula is changed in C6 & C7 as well.

In short – how do I apply a moderately different formula between filtered cells without effecting the hidden ones? Or is there a better way to do what I want.

=IF(ISERROR(VLOOKUP1),VLOOKUP2,VLOOKUP1)