Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-13-2022, 12:43 PM
djlw84 djlw84 is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Mac OS X Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Novice
Excel VLOOKUP - Return cells that are not blank from range of specified cells
 
Join Date: Jan 2022
Posts: 4
djlw84 is on a distinguished road
Default Excel VLOOKUP - Return cells that are not blank from range of specified cells

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
Reply With Quote
  #2  
Old 04-13-2022, 11:56 PM
ArviLaanemets ArviLaanemets is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 8 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

The first formula has 7 nested IF's, what is a max number of nesting allowed!

Workarounds:
a) Use 'subnesting', i.e. use an IF() to divide your single-column conditions into 2 or more groups of conditions, and then use nested IF's <like = IF(StartCondition, IF(... subnest 1 ...), IF(... subnest 2 ...))>;
b)Use formula instead like
Code:
(VLOOKUP(...)&""="")*(VLOOKUP(...)&""="")*...*VLOOKUP(...)
+
(VLOOKUP(...)&""="")*(VLOOKUP(...)&""="")*...*VLOOKUP(...)
+
...
c) Use helper column in your table to calculate the number of return column of VLOOKUP. Your formula will be like
Code:
=VLOOKUP(A219,Form1!$A:$X,HelperColumnValue,FALSE)
Reply With Quote
  #3  
Old 04-14-2022, 12:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 7 64bit Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by ArviLaanemets View Post
The first formula has 7 nested IF's, what is a max number of nesting allowed!

As a matter of fact modern versions of XL ( starting at 2007 I think) will accept 64 levels of nested IF
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 04-14-2022, 12:22 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 7 64bit Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Quote:
Originally Posted by djlw84 View Post
Hi,

I have a nested IF VLOOKUP formula that has been ......

Hello
please post a sample sheet with some data and some manually calculated results and explain clearly what you are trying to do. The above formulas are a bit "messy"...
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 04-14-2022, 02:02 AM
ArviLaanemets ArviLaanemets is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 8 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Pecoflyer View Post
As a matter of fact modern versions of XL ( starting at 2007 I think) will accept 64 levels of nested IF
After this was pointed out, I did find this information from MS Help (had to go to another page using a link, and reading through text there).

In past, there was the limit for number of nested IF's declared at end of MS Help for IF function. Not anymore! (Btw, I searched for this limit earlier, and couldn't find it then.)
Reply With Quote
  #6  
Old 04-14-2022, 03:07 AM
p45cal's Avatar
p45cal p45cal is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 10 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

If you've got the LET and FILTER functions available in your version of Excel you can try:
Code:
=IFERROR(LET(b,Form1!$A:$AK,a,INDEX(b,MATCH(A219,INDEX(b,0,1)),{11,14,15,16,17,18,19,20}),INDEX(FILTER(a,NOT(ISBLANK(a))),1)),"")
where the {11,14,15,16,17,18,19,20} is the column numbers of interest where you add the column numbers at will. If you're looking for the columns to be searched in a different order then change the order in the curly brackets.
Reply With Quote
  #7  
Old 04-14-2022, 04:11 AM
p45cal's Avatar
p45cal p45cal is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 10 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

it looks like you missed one close bracket:
Code:
=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)), "")
Why the:

Form1!$A:$AK,11,FALSE)&""="",IF(VLO
and not just:
Form1!$A:$AK,11,FALSE)="",IF(VLO


Code:
=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)), "")
Reply With Quote
  #8  
Old 04-14-2022, 01:36 PM
djlw84 djlw84 is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Mac OS X Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Novice
Excel VLOOKUP - Return cells that are not blank from range of specified cells
 
Join Date: Jan 2022
Posts: 4
djlw84 is on a distinguished road
Default

Please see attached sample workbook.

Form1 is where users forms requests are being recorded.Sheet 2 is where I want the formula to be applied (in Column H). I have put the desired result in the column.

I want to lookup the ID number in Column A of Sheet 2 against Form1 and return the client name from one of the 12 cells that could contain a client name (only one of these 12 cells will ever have a value in).

As I mentioned before, the formula was working fine before I tried to add the four new column/cell references (AH, AI, AJ and AK).

Hopefully this makes sense - happy to clarify further if needed!
Attached Files
File Type: xlsx Sample Workbook.xlsx (27.7 KB, 8 views)
Reply With Quote
  #9  
Old 04-14-2022, 04:30 PM
p45cal's Avatar
p45cal p45cal is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 10 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Yes,
Code:
=IFERROR(LET(b,Form1!$A:$AK,a,INDEX(b,MATCH(A2,INDEX(b,0,1)),{11,14,15,16,17,18,19,20,34,35,36,37}),INDEX(FILTER(a,NOT(ISBLANK(a))),1)),"")
works here.
Reply With Quote
  #10  
Old 05-09-2022, 08:10 AM
djlw84 djlw84 is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Mac OS X Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2019
Novice
Excel VLOOKUP - Return cells that are not blank from range of specified cells
 
Join Date: Jan 2022
Posts: 4
djlw84 is on a distinguished road
Default

Thank you so much!
Reply With Quote
  #11  
Old 05-09-2022, 11:59 AM
spillerbd spillerbd is offline Excel VLOOKUP - Return cells that are not blank from range of specified cells Windows 10 Excel VLOOKUP - Return cells that are not blank from range of specified cells Office 2013
Competent Performer
 
Join Date: Jan 2016
Posts: 130
spillerbd is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
The first formula has 7 nested IF's, what is a max number of nesting allowed!
Nested ifs are limited to 7 in the 32-bit version. Using the 64-bit version allows 64 levels of nesting.
64-bit is the preference for Excel and has recently become accepted. I have been 64-bit since, oh I forget when, 2010 I think.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VLOOKUP - Return cells that are not blank from range of specified cells have a formula that combines two cells return a zero instead of a blank cell Kubi Excel 3 08-07-2017 11:52 PM
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) slaycock Word VBA 0 02-18-2017 07:00 AM
Excel VLOOKUP - Return cells that are not blank from range of specified cells Vlookup fill down blank cells in column mbesspiata Excel Programming 8 09-26-2016 04:44 AM
Excel VLOOKUP - Return cells that are not blank from range of specified cells Excel Formula: return a range of cells that match tinfanide Excel 4 08-30-2014 07:03 AM
Copying text range of cells to different cells adds an extra line jpb103 Word VBA 2 07-23-2014 12:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:59 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft