Excel VLOOKUP - Return cells that are not blank from range of specified cells

[djlw84]

Hi,

I have a nested IF VLOOKUP formula that has been set up to return the only cell that has a value in from a range of specified cells - the formula is as follows and has been working correctly:

=IFERROR( IF(VLOOKUP(A219,Form1!$A:$X,11,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,14,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,15,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,16,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,17,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,18,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$X,19,FALSE)&""="", VLOOKUP(A219,Form1!$A:$X,20,FALSE), VLOOKUP(A219,Form1!$A:$X,19,FALSE)), VLOOKUP(A219,Form1!$A:$X,18,FALSE)), VLOOKUP(A219,Form1!$A:$X,17,FALSE)), VLOOKUP(A219,Form1!$A:$X,16,FALSE)), VLOOKUP(A219,Form1!$A:$X,15,FALSE)), VLOOKUP(A219,Form1!$A:$X,14,FALSE)), VLOOKUP(A219,Form1!$A:$X,11,FALSE)), "")

I need to extend this formula to consider 4 new cells in the calculation (columns 34,35,36 and 37) . Using the above logic, I created the following formula:

=IFERROR( IF(VLOOKUP(A219,Form1!$A:$AK,11,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,14,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,15,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,16,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,17,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,18,FALSE)&""="", IF(VLOOKUP(A219,Form1!$A:$AK,19,FALSE)&""="",
IF(VLOOKUP(A219,Form1!$A:$AK,20,FALSE)&""="",
IF(VLOOKUP(A219,Form1!$A:$AK,34,FALSE)&""="",
IF(VLOOKUP(A219,Form1!$A:$AK,35,FALSE)&""="",
IF(VLOOKUP(A219,Form1!$A:$AK,36,FALSE)&""="",VLOOK UP(A219,Form1!$A:$AK,37,FALSE), VLOOKUP(A219,Form1!$A:$AK,36,FALSE)), VLOOKUP(A219,Form1!$A:$AK,35,FALSE)), VLOOKUP(A219,Form1!$A:$AK,34,FALSE)),


VLOOKUP(A219,Form1!$A:$AK,20,FALSE), VLOOKUP(A219,Form1!$A:$AK,19,FALSE)), VLOOKUP(A219,Form1!$A:$AK,18,FALSE)), VLOOKUP(A219,Form1!$A:$AK,17,FALSE)), VLOOKUP(A219,Form1!$A:$AK,16,FALSE)), VLOOKUP(A219,Form1!$A:$AK,15,FALSE)), VLOOKUP(A219,Form1!$A:$AK,14,FALSE)), VLOOKUP(A219,Form1!$A:$AK,11,FALSE)), "")

However, when I've entered this I get a "you've entered too many arguments for this function" error message.

Ive been back through the syntax however cannot see where I have gone wrong.

Can anyone help? Happy to share a copy of the workbook