I am searching for a value from one table to another (the value is located in
Column A in Sheet2 and column H in Sheet1). The formula seen below is what I
have been using (in Sheet3)

=IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,F ALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"")


the IF statement will convert a null value to a blank space instead of a
zero. But, if the value that im searching for is deleted in Sheet2, i get an
#N/A. Shouldnt this formula just skip a deleted row and go to the next?
(the row is completely deleted....it is not a blank row)


here is what the formula looks like after i try to search for that missing
value:


=IF(ISNA(VLOOKUP(Sheet2!#REF!,Sheet1!$H$2:$H$9000, 1,FALSE)),IF(Sheet2!#REF!
= "", "", Sheet2!#REF!),"")

excel places that #REF! in my formula if the value USED to exist and then
does not anymore.


any ideas on how to fix this?


thanks in advance!