Intermittant #N/A error in nested vlookup index formula that shouldn't occur

[DPKologie]

I've been using a nested Vlookup Index formula that has worked perfectly, however now I have adapted it in another worksheet to similar data (merely a different time frame) and I am getting intermittant #N/A errors, while most of the results are coming out fine. The columns are NOT formatted as text; one is formatted as general, the other is formatted as number. There doesn't seem to be a pattern as to the errors; they are occuring no more than once per row and nearly but not all rows, and in different columns. The data is available (not blank) in the cells being referred to.

Here is the first row and column of the formula: =VLOOKUP(B$1,INDEX(TransferPIWeekly!$A:$A,(ROWS(B$2:B2)-1)*8+2):INDEX(TransferPIWeekly!$B:$B,ROWS(B$2:B2)*8+1),2,0)

Any help would be most welcome! This has me totally perplexed.

Stevie the Kid

[Haseeb Avarakkan]

Hello Steve,

Do you have B1 occur in TransferPIWeekly!A2:A9? if yes make sure no trailing or leading spaces.

[DPKologie]

That was an excellent tip, Haseeb! I checked for trailing or leading spaces, but that wasn't the case.

It turns out I had duplicate data in my new worksheet that threw the rows off by one row, so the VLOOKUP didn't work within the INDEX row range. (I'd copied and moved data up a row, but had neglected to delete the duplicate entry at the end, then I added new data at the bottom.) That's why I had intermittant errors where an Index wasn't found in each range of 8 rows from that point on.

Thanks so much for this tip! I learned something and will now check for trailing and leading spaces in the future. And you also pointed me to check if the B$1 (and others) index occurred within each range, and I was able to isolate and identify the problem. Many, many thanks!

I love this forum, and you Gurus are awesome!