Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Excel VLOOKUP - Return cells that are not blank from range of specified cells
Register FAQ

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: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
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,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
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
__________________
Using O365 v2503 - 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,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
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"...
__________________
Using O365 v2503 - 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: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
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: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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, 10 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: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
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

« Previous Thread | Next Thread »


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 09:43 AM.

Contact Us - Privacy Statement - Top

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