Excel - Formula to return only cell with value from specified range

[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)&""="",VLOOKUP(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

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel - Formula to return only cell with value from specified range
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[djlw84]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Excel - Formula to return only cell with value from specified range
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My apologies - I wasn't aware. Won't happen again.