I am getting an error message box when attempting a VLOOKUP, Index Match, or XLOOKUP

Question

I've never encountered this kind of issue before. Working in Excel 365 x64.

I currently have the following formula in a cell and it is working properly:

=IFERROR(IF(D9=""|""|(VLOOKUP($D9|SpecialistInfo|6|FALSE))-(SUM((H9*$H$7)|(I9*$I$7)|(J9*$J$7)|(K9*$K$7)|(L9*$L$7)|(N9*$N$7)|(O9*$O$7)|(P9*$P$7)|(Q9*$Q$7)|(R9*$R$7)|(S9*$S$7)|(T9*$T$7)|(U9*$U$7)|(V9*$V$7)|(W9*$W$7)|(X9*$X$7)|(Y9*$Y$7)|(Z9*$Z$7)|(AA9*$AA$7)|(AB9*$AB$7)|(AC9*$AC$7)|(AD9*$AD$7)|(AE9*$AE$7)|(AF9*$AF$7)|(AG9*$AG$7)|(AH9*$AH$7)|(AI9*$AI$7)|(AJ9*$AJ$7)|(AK9*$AK$7)|(AL9*$AL$7)|(AM9*$AM$7)|(AN9*$AN$7)|(AO9*$AO$7)|(AP9*$AP$7)|(AQ9*$AQ$7)|(AR9*$AR$7)|(AS9*$AS$7)|(AT9*$AT$7)|(AU9*$AU$7)|(AV9*$AV$7)|(AW9*$AW$7)|(AX9*$AX$7)|(AY9*$AY$7)|(AZ9*$AZ$7)|(BA9*$BA$7)|(BB9*$BB$7)|(BC9*$BC$7)))-IF(BI9>0|120|0))|"")

However, when I try to do a simple VLOOKUP in a different cell, I get the following error:

The error seems to be pointing to the Col_Index_Num portion of the VLOOKUP formula... so it's looking at Column 6 and column 6 is populated.

Keep in mind that this is the same VBA that is nested into the IF and IFERROR formulas above. So we know the Table Array is fine.

NOTE: Even though the formula is working right now, I get the same error when I try to add another column to the nested SUM formula (e.g. |(BC9*$BD$7)).

Has anyone ever encountered this issue? Please help!

I've tried an IF formula in the same cell where I had the simple VLOOKUP formula. The IF formula works in that cell.

I get the same issue when I perform an Index Match or an XLOOKUP instead of the VLookup.

I have checked for issues in the Table Array.

I have checked for issues in the Table Naming convention/Named Range.

Answer 1

This is due to blank cells in the range you're summing the products over.

This is at least when I convert your blanks to "" values using a function.

Your syntax is also completely incompatible with my Excel (Office 365) version. I would be surprised if this was not generally the case - the first time I've ever seen or heard of syntax like this for Excel.

PS - Whilst outside the scope of this question, it is worth noting that you should make (better) use of far more robust functions available at your fingertips.

For instance, the portion of code relating to non-blanks etc. compresses to:

=LET(x1_,H7:BC7,x2_,H9:BC9,y_,IFERROR(IF(D9=",",(VLOOKUP($D9,SpecialistInfo,6,FALSE))-SUM(x1_*x2_)-IF(BI9>0,120,0)),""),SUM(IFERROR(x1_*x2_,"")))