1

How do you force the focus of an Excel Cell to remain the same?

I have a question in the same reference above link. I am trying to match the data I have in Column A and Column B. If data matches it says "Record Matched". If not it says "Record Not Found". Though my issue is that the formula changes it lookup/range values when I insert a new cell in column A

For example, if I insert a cell in Column A to match the cell value in column B, the formula changes its range. I tried to using $ and range.

My end plan is if I insert a cell to move the value down, than the next row should change from "Record Not Found" to "Record Matched"

I tried to explore the Indirect but didn't get the formula correct. I am hoping there is value we can subtract the row to match row values when I insert one or two cell rows.

Any help appreciated

=IF(ISNA(VLOOKUP(B3,A3,2,FALSE)), "Record Not Found", "Record Matched")

Column A   Column B  Diff 
1.1.1.1    1.1.1.1   Record Matched 
3.3.3.3    2.2.2.2   Record Not Found 
4.4.4.4    3.3.3.3   Record Not Found 
4.4.4.4              Record Not Found
1
  • Welcome to Superuser. As a comment, you don't need to use VLOOKUP to compare if 2 cells on the same row are equal. IF(A3=B3, "Not Found", "Matched") is equivalent. And if this is what you want, Mark's solution should work.
    – mtone
    Mar 14, 2016 at 5:49

1 Answer 1

1

I'm pretty sure I understand what you are asking, so perhaps try the below.

To prevent excel from changing the row it compares when you insert and shift cells down, you need the formula to use the current row. This can be done with row(), which returns an integer with the row number.

=IF(INDEX($1:$65536,ROW(),1)=INDEX($1:$65537,ROW(),2),"Record Matched","Record Not Found")

Explanation: The INDEX function replaces direct cell references and does not get changed when inserting or deleting rows. It takes 3 parameters: range of cells, row number, column number. Here, the range is all rows from 1 to 65536, the row number is row(), and the column number is 1 (A) or 2 (B).

This is how it looks before inserting a cell

enter image description here

And this is what happens after you insert a blank cell, shifting cells down at A4

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .