Excel Index-Match not consistent?

Question

Currently, I am searching through tables of RNAseq data and matching up data points across the tables. I have a column which is currently used to pull sequence data from a reference table on a separate worksheet. To do this I have used the following code:

=IFERROR(INDEX(SheetB!A:A, MATCH("*"&A2&"*", SheetB!A:A,0)+1), " ")

My issue is that this code works for around three quarters of the table, but returns N/A errors for the rest. The data that the code cannot find can be found manually in the correct worksheet by searching the worksheet with the value of the reference cell ('A2' in the above example).

I originally assumed that the returned value was too long, as it is nucleotide sequence data with each row containing over a thousand characters, but many rows have returned okay at ~900 characters.

I would go through omitted values by hand and fill them in, but the data set has roughly 200k lines, and I do not have the time to correct each failed instance. Is there a flaw in how I'm attempting to return the data which is causing it to work in some instances and not others?

Answer 1

What about if you make a duplicate from the table, and delete there let's say 50% of the rows starting from the beginning? Do you have still the same behavior? Do you use named areas - check it with the Excel Name Manager. It's rather painfull to find such an error without having the table to check.