|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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(...) + ... Code:
=VLOOKUP(A219,Form1!$A:$X,HelperColumnValue,FALSE) |
#3
|
||||
|
||||
Quote:
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 |
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
Quote:
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.) |
#6
|
||||
|
||||
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)),"") |
#7
|
||||
|
||||
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)), "") 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)), "") |
#8
|
|||
|
|||
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! |
#9
|
||||
|
||||
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)),"") |
#10
|
|||
|
|||
Thank you so much!
|
#11
|
|||
|
|||
Quote:
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
Vlookup fill down blank cells in column | mbesspiata | Excel Programming | 8 | 09-26-2016 04:44 AM |
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 |