INDEX and MATCH formula data limit?

[sspatriots]

Hi,

I have a formula (shown below) that seems to have a limit on the number of rows it recognizes. Never seen this before using these formulas. The table "Completed__2" in the second line only shows the first 62 rows of data when I use F9 in the formula bar where the table_array is in the VLOOKUP. The item that it should match with is at row 286 of that table_array. So the logical_test for the line results in "TRUE" that it is an error. Shouldn't I be able to see the data set of the entire table when I use F9 in the formula bar over the table_array of the VLOOKUP? Any suggestions would be greatly appreciated. Hopefully there is something obvious here that I'm just missing.


Thanks, SS

=IF([@[G1
Job '#]]="","",

IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Actual Completion Date", Completed__2[#Headers],0),FALSE)),"D",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Actual Completion Date", Completed__2[#Headers],0),FALSE)),
IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),JobList[#Data],MATCH("Completion Date",JobList[#Headers],0),FALSE)),"HOLD",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),JobList[#Data],MATCH("Completion Date",JobList[#Headers],0),FALSE)),"B",
IF(ISBLANK(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),JobList[#Data],MATCH("Ship Date",JobList[#Headers],0),FALSE)),"RLSD TO PROD",
IF(ISERROR(VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),JobList[#Data],MATCH("Ship Date",JobList[#Headers],0),FALSE)="TBD"),

VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),JobList[#Data],MATCH("Completion Date",JobList[#Headers],0),FALSE),"F")))),
VLOOKUP(@INDEX(G2JobList,ROW()-2,MATCH("G1" & CHAR(10) & "Job #",G2JobList[#Headers],0)),Completed__2[#Data],MATCH("Actual Completion Date", Completed__2[#Headers],0),FALSE))))

 

[sspatriots]

I think I found a solution. I used XLOOKUP instead. Seemed easier when I was done and it appears to be working so far. If I run into another issue, I'll come back and update this post.