How to correct a #REF! error - Microsoft Support
The #REF! error shows when a formula refers to a cell that’s not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over. #REF! error caused by deleting a column. Example - VLOOKUP with incorrect range references. INDEX with incorrect row or column reference. Referencing a closed workbook with INDIRECT
XMATCH function - Microsoft Support
The XMATCH function searches for a specified item in an array or range of cells, and then returns the relative position of the first match.
How to correct a #REF! error - Microsoft Support
The #REF! error shows when a formula refers to a cell that’s not valid. This happens most often when cells that were referenced by formulas get deleted, or pasted over. #REF! error caused by deleting a column. Example - VLOOKUP with incorrect range references. INDEX with incorrect row or column reference. Referencing a closed workbook with INDIRECT